Victor Blomqvist | 9 Oct 08:32 2015

Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

I have a heavily used PostgreSQL 9.3.5 database on CentOS 6. Sometimes I need to add/remove columns, preferably without any service interruptions, but I get temporary errors.

I follow the safe operations list from but many operations cause troubles anyway when the more busy tables are updated.

Typically I have user defined functions for all operations, and my table and functions follow this pattern:

  id integer PRIMARY KEY,
  name varchar NOT NULL,
  to_be_removed integer NOT NULL

CREATE FUNCTION select_users(id_ integer) RETURNS SETOF users AS
$$ LANGUAGE plpgsql;

Then the actual queries are run by our application as

SELECT id, name FROM select_users(18);

As you can see the column to_be_removed is not selected. Then to remove the column I use:

ALTER TABLE users DROP COLUMN to_be_removed;

However, while the system is under load sometimes (more frequently and persistent the more load the system is experiencing) I get errors like these:

    ERROR #42804 structure of query does not match function result type: Number of returned columns (2) does not match expected column count (3).

The same error can happen when columns are added. Can this be avoided somehow, or do I need to take the system offline during these kind of changes?

For reference, there was a similar but not same issue posted to psql-bugs a long time ago: <at>

I posted this same question at dba.stackexchange and got the advice to repost here:

Selim Tuvi | 9 Oct 00:54 2015

BDR: no free replication state could be found

Hi I am testing BDR functionality with Postgres 9.4. I had went through the bdrdemo example with a 3 node cluster and then tried to set up my own db.

My "max_replication_slots" is set to 6. After getting removing the bdrdemo db I am having trouble starting up the postgres instance unless I increase the value of "max_replication_slots". I get the following error in the log:

"starting up replication identifier with ckpt at 0/28E8250",,,,,,,,,""
"recovered replication state of node 1 to 0/54DDCD0",,,,,,,,,""
"recovered replication state of node 2 to 0/1ECBEA0",,,,,,,,,""
"recovered replication state of node 3 to 0/59FB1C0",,,,,,,,,""
"recovered replication state of node 4 to 0/2AA5320",,,,,,,,,""
"recovered replication state of node 5 to 0/27F2F98",,,,,,,,,""
"recovered replication state of node 6 to 0/59F35A8",,,,,,,,,""
"no free replication state could be found, increase max_replication_slots",,,,,,,,,""

pg_replication_slots is only reporting two slots:

postgres=# SELECT * FROM pg_catalog.pg_replication_slots;
                slot_name                | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn
 bdr_19685_6199712740068695651_1_18817__ | bdr    | logical   |  19685 | deliver  | t      |      |         2280 | 0/28EA5E0
 bdr_19685_6197393155020108291_1_48609__ | bdr    | logical   |  19685 | deliver  | t      |      |         2280 | 0/28EA5E0

How can I get rid of the stale node recovery on startup?


Jeff Janes | 8 Oct 23:47 2015

Version management for extensions

I am facing a scenario where I have different version of an extension, say 1.0 and 2.0, which have some different functionality between them (so not merely a bug fix), so people might want to continue to use 1.0.

But changes to the PostgreSQL software between major versions requires changes to the extension's source code.

So I  basically have 4 versions to carry:


Is there some easy way to handle this?  Are there examples of existing modules which have a similar situation (and which handle it well) on PGXN or pgfoundry or other public repositories?


Emi | 8 Oct 21:51 2015

blank in query - cannot return results


PostgreSQL 8.3.18  on x86_64-linux-gnu, query:

select * from table_name where col1 =    'Abc, test';
select * from table_name where col1 ilike '%Abc, test%';

NO result returned.

But run:
select * from table_name where col1 ilike '%Abc,%test%';   -- remove blank

Result is returned.

PSQL terminal encoding: unicode (UTF-8). From both terminal and db, 
values shows/and displayed as "Abc, test".

But " " space/blank in where section cannot return query result.

Can someone tell why may cause the issue please?

Thanks a lot!


Sent via pgsql-general mailing list (pgsql-general <at>
To make changes to your subscription:

Paul Ramsey | 7 Oct 21:25 2015

PostGIS 2.2.0 Released

PostGIS 2.2.0 is released! Over the last two years a number of interesting new features have been added,
such as:

* True nearest-neighbor searching for all geometry and geography types
* New volumetric geometry support, including ST_3DDifference, ST_3DUnion and more
* Temporal data model support and functions like ST_ClosestPointOfApproach to support temporal query
* Spatial clustering functions ST_ClusterIntersecting and ST_ClusterWithin
* Subdividing large geometries with ST_Subdivide
* Fast box clipping with ST_ClipByBox2D
* In-database raster processing with ST_Retile and ST_CreateOverview
* New high-speed native code address standardizer
* Visvalingam-Whyatt geometry simplification with ST_SimplifyVW
* Support for compressed “tiny well-known binary” format with ST_AsTWKB and ST_GeomFromTWKB

See the full list of changes in the news file:

Thanks to everyone who helped with testing and development over this cycle!

Team PostGIS


Sent via pgsql-general mailing list (pgsql-general <at>
To make changes to your subscription:

john.tiger | 7 Oct 17:58 2015

using postgresql for session

has anyone used postgres jsonb for holding session ?  Since server side 
session is really just a piece of data, why bother with special 
"session" plugins and just use postgres to hold the data and retrieve it 
with psycopg2 ?  Maybe use some trigger if session changes?    We are 
using python Bottle with psycopg2 (super simple, powerful combo) - are 
we missing something magical about session plugins ?


Sent via pgsql-general mailing list (pgsql-general <at>
To make changes to your subscription:

Andrus | 7 Oct 16:48 2015

Re: How to drop user if objects depend on it


>Can you connect as user postgres? IE: psql -U postgres -d <yourdb>

Applicaton has admin users which should be able to delete other users.
Those users dont have superuser rights.

I can connect as user postgres for testing only.
I'm looking for a way to delete users without superuser right.

>If so, then you should have the ability to execute the commands without any 

I tried in database ktp :

reassign owned by farukkugay to postgres;
drop user farukkugay ;

This causes error

ERROR:  role "farukkugay" cannot be dropped because some objects depend on 
DETAIL:  privileges for schema public

So even superuser cannot delete.



Sent via pgsql-general mailing list (pgsql-general <at>
To make changes to your subscription:

Bernd Helmle | 7 Oct 11:20 2015

Issues with german locale on CentOS 5,6,7

The last day we've encountered an issue what i think is somewhat severe if
you want to do either OS upgrades with CentOS or even binary upgrades with
an existing PostgreSQL instance to a new machine with locale de_DE.UTF-8
and thus i'd like to share here. 

Here are the details:

Originally a Postgres 9.4 was running on CentOS 5.11/x86_64. The database
in question was initialized with locale de_DE.UTF-8 and previously upgraded
via pg_upgrade from 9.2 and then running without any issues for a while.

After that the customer migrated to new hardware with an OS upgrade to
CentOS 6.6/x86_64. This was done by just remounting the SAN LUN on the new
machine. So far so good, no issues. 

However, after a while developers realized duplicate values in unique keys
with certain types of string values (the format is described in the
examples below). So the suspicion was that this has to do with locales. And
yes, the german locale collation order changed:

CentOS 5.11 has:

echo -e '156\n1-5-6\n110\n1-1-0' | LANG=de_DE.UTF-8 sort

CentOS 6.6 does:

echo -e '159\n1-5-9\n110\n1-1-0' | LANG=de_DE.UTF-8 sort

Interestingly CentOS 7.1 restores the behavior from CentOS 5.11

echo -e '159\n1-5-9\n110\n1-1-0' | LANG=de_DE.UTF-8 sort

There are entries in the CentOS bugtracker regarding other locales:

So users are encouraged to carefully test their platforms when upgrading.
Checks show that at least RHEL6 and RHEL7 have the same issue, too.




Sent via pgsql-general mailing list (pgsql-general <at>
To make changes to your subscription:

Steve Pribyl | 7 Oct 05:28 2015


Good Evening,

What do I need to do to recover a database server that has a backup.old file in the data_directory.  I have see
references to a database being stopped during a backup and/or a second backup running and moving the old
file aside, but it was not clear to me what needs to be done do recover.

postgresql-9.3                   9.3.0-2.pgdg12.4+1


Steve Pribyl | Senior Systems Engineer
Akuna Capital LLC
36 S Wabash, Suite 310 Chicago IL 60603 USA | <>
p: +1 312 994 4646 | m:  | f: +1 312 750 1667 | Steve.Pribyl <at>

Please consider the environment, before printing this email.

This electronic message contains information from Akuna Capital LLC that may be confidential, legally
privileged or otherwise protected from disclosure. This information is intended for the use of the
addressee only and is not offered as investment advice to be relied upon for personal or professional use.
Additionally, all electronic messages are recorded and stored in compliance pursuant to applicable SEC
rules. If you are not the intended recipient, you are hereby notified that any disclosure, copying,
distribution, printing or any other use of, or any action in reliance on, the contents of this electronic
message is strictly prohibited. If you have received this communication in error, please notify us by
telephone at (312)994-4640 and destroy the original message.


Sent via pgsql-general mailing list (pgsql-general <at>
To make changes to your subscription:

Oleksii Kliukin | 6 Oct 22:13 2015

dubious optimization of the function in SELECT INTO target list


I have an issue with a function that is called as a part of the SELECT
INTO target list in pl/pgSQL. I'd like to illustrate it with a simple

DO $$ 
DECLARE l_id integer;
       SELECT test(id) INTO l_id
        FROM generate_series(1,10) t(id);
$$ LANGUAGE plpgsql;

It looks like the test function in this example is executed only once.
In order to check this, one can define the test function as following:

CREATE TABLE foo(id integer);

CREATE OR REPLACE FUNCTION public.test(id integer)
RETURNS integer
LANGUAGE plpgsql
        INSERT INTO foo VALUES($1);
        RETURN $1;

Basically, if we invoke the first example, the foo table with have only
1 row and not 10, as supplied by the generate_series.
However, when ORDER BY is attached to the query, or aggregate (such as
max, min or array_agg) is wrapped around the test(id) call, the test
function is called exactly 10 times. If I replace the SELECT INTO with
PERFORM, it would also be called 10 times. Unfortunately, it is not
possible to use PERFORM directly in the CTE expression.

The documentation on the SELECT INTO suggests that the rows returned by
the test function may be discarded after the first one:

"If STRICT is not specified in the INTO clause, then target will be set
to the first row returned by the query, or to nulls if the query
returned no rows. (Note that "the first row" is not well-defined unless
you've used ORDER BY.) Any result rows after the first row are

However, it does not say anything about the number of rows the query
target list will be evaluated, meaning one may expect it to be evaluated
more than once. It seems that in the case of the example above
optimizing out calls to the 'test' function would only produce an
expected result if the function itself does not have any side-effects,
e.g.. qualifies as 'stable' or 'immutable'.

Is there some (undocumented) restriction on the functions allowed in the
SELECT target list, and isn't the optimization to limit the number of
calls to 'test' to 1 wrong in this case?

Kind regards,


Sent via pgsql-general mailing list (pgsql-general <at>
To make changes to your subscription:

droberts | 6 Oct 20:53 2015

Re: Best practices for aggregate table design

Okay, so is it safe to say I should use loosely use these guidelines when
deciding whether to model an attribute as a dimension
(type=[inbound,outbound]) vs. bundling with a measure (total_inbound) ?

If you know the number of values for a dimension are fixed (e.g. boolean),
then creating a measure will have benefits of:
  - reduced number of rows/storage
  - better performance since less indexing/vacuuming  

the drawbacks are:
  -rigid structure, not very extensible over time (e.g. later realize I need
to also track 'internal' calls). 

In my case, I'm now needing to add another measure 'encrypted=true/false',
so my table is starting to look like 

month | city_id | state_id | total_calls_inbound | total_calls_outbound | 
total_calls_inbound_encr | total_calls_outbound_encr | 

getting a bit hairy but the alternative seems like it would start growing
too quickly in rows and more I/O for inserts.  

month | city_id | state_id | encrypted  | type  |  total_calls

2015-01 12 2 true, false, 56
2015-01 10 4  true, true, 147 
2015-01 null null 201 17 218 

View this message in context:
Sent from the PostgreSQL - general mailing list archive at


Sent via pgsql-general mailing list (pgsql-general <at>
To make changes to your subscription: