Re: [GENERAL] index bloat question

[Szymon Guz]

On 17 October 2011 02:01, Scott Marlowe  wrote:

> On Sun, Oct 16, 2011 at 2:41 PM, Szymon Guz  wrote:
> > Hi,
> > just a couple of questions:
> > will there be an index bloat if I have:
> > - a serial column and only add rows to the table?
> > - a text column and I only add rows to the table?
> > For the serial column the numbers are only incremented, for the text
> column
> > I add random strings.
>
> With no deletes or updates, the only bloat will be from a non 100% fill
> factor.
>

Hi Scott,

if there is no bloat, how could you explain this:

Simple test:

CREATE TABLE test (
id text primary key,
category_id text not null
);

CREATE INDEX i_category ON test (category_id);


I make 500k inserts in one transaction using a python script.
For the random text I use random uuid from the function:

uuid.uuid4()

After those inserts I create another index:

CREATE INDEX i_new ON test (category_id);


select
pg_size_pretty(pg_relation_size('i_category')),
pg_size_pretty(pg_relation_size('i_new'))
;

Results:

'37 MB';'28 MB'

regards
Szymon

[GENERAL] plpgsql; execute query inside exists

[jozsef . kurucz]

Hi there,

I would like to use EXISTS in a small plpgsql function but I always
get a "syntax error". How can I execute a query inside the
EXISTS function?



IF NOT EXISTS(EXECUTE 'SELECT * FROM '|| tmp_tbl)
   THEN
  CREATE TABLE tt();




ERROR:  syntax error at or near "EXECUTE"
LINE 1: SELECT  NOT EXISTS(EXECUTE 'SELECT * FROM '||  $1 )



Sorry for my lame question but I'm new in postgres.

Thanks!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using constraint exclusion with 2 floats

[Simon Riggs]

On Wed, Oct 12, 2011 at 10:40 PM, Julien Rouhaud  wrote:
> Thank you for your answer.
>
> I'm sorry I really didn't explained well my problem :/
>
> For example if I have a table test like this :
> CREATE TABLE test (min real not null, max real not null, desc character
> varying not null);
>
> and I want a constraint exclusion to make sure the range min/max doens't
> overlap
>
> I can't write ALTER TABLE test add constraint test_exclude EXCLUDE USING
> btree ((min,max) WITH &&)
>
> I saw the extension temporal gives a new type PERIOD and has operators like
> &&, but only for timestamp, so I'm wondering if I must code something
> something similar or if there's is an easier way

You've confused "exclusion constraints" with "constraint exclusion",
which does seem easy to do.

"Exclusion constraints" are not limited to a single datatype either,
so you should be able to find a solution.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plpgsql; execute query inside exists

[Merlin Moncure]

On Mon, Oct 17, 2011 at 2:32 AM,   wrote:
> Hi there,
>
> I would like to use EXISTS in a small plpgsql function but I always
> get a "syntax error". How can I execute a query inside the
> EXISTS function?
>
>
>
> IF NOT EXISTS(EXECUTE 'SELECT * FROM '|| tmp_tbl)
>   THEN
>      CREATE TABLE tt();
>
>
>
>
> ERROR:  syntax error at or near "EXECUTE"
> LINE 1: SELECT  NOT EXISTS(EXECUTE 'SELECT * FROM '||  $1 )


EXECUTE is a top level statement -- you can't run it inside a query
like that.  Also, EXISTS is not a way to check to see if a table does
exist -- it is a clause for the presence of a row and returns true if
it finds one -- but if the table does not exist you would get an SQL
error.

A better way to do this is to query information_schema:

PERFORM 1 FROM information_schema.tables where schema_name = x and
table_name = y;

IF FOUND THEN
  CREATE TABLE ...
END IF;

(there is a race condition in the above code -- do you see it? if
concurrent access to this function is an issue, you have to LOCK an
object before running the PERFORM or perhaps use an advisory lock).

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plpgsql; execute query inside exists

[Merlin Moncure]

On Mon, Oct 17, 2011 at 8:20 AM, Merlin Moncure  wrote:
> On Mon, Oct 17, 2011 at 2:32 AM,   wrote:
>> Hi there,
>>
>> I would like to use EXISTS in a small plpgsql function but I always
>> get a "syntax error". How can I execute a query inside the
>> EXISTS function?
>>
>>
>>
>> IF NOT EXISTS(EXECUTE 'SELECT * FROM '|| tmp_tbl)
>>   THEN
>>      CREATE TABLE tt();
>>
>>
>>
>>
>> ERROR:  syntax error at or near "EXECUTE"
>> LINE 1: SELECT  NOT EXISTS(EXECUTE 'SELECT * FROM '||  $1 )
>
>
> EXECUTE is a top level statement -- you can't run it inside a query
> like that.  Also, EXISTS is not a way to check to see if a table does
> exist -- it is a clause for the presence of a row and returns true if
> it finds one -- but if the table does not exist you would get an SQL
> error.
>
> A better way to do this is to query information_schema:
>
> PERFORM 1 FROM information_schema.tables where schema_name = x and
> table_name = y;
>
> IF FOUND THEN
>  CREATE TABLE ...
> END IF;

oops.. meant to say IF NOT FOUND... :-).

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] index bloat question

[Merlin Moncure]

On Mon, Oct 17, 2011 at 2:15 AM, Szymon Guz  wrote:
>
>
> On 17 October 2011 02:01, Scott Marlowe  wrote:
>>
>> On Sun, Oct 16, 2011 at 2:41 PM, Szymon Guz  wrote:
>> > Hi,
>> > just a couple of questions:
>> > will there be an index bloat if I have:
>> > - a serial column and only add rows to the table?
>> > - a text column and I only add rows to the table?
>> > For the serial column the numbers are only incremented, for the text
>> > column
>> > I add random strings.
>>
>> With no deletes or updates, the only bloat will be from a non 100% fill
>> factor.
>
> Hi Scott,
>
> if there is no bloat, how could you explain this:
>
> Simple test:
>
> CREATE TABLE test (
> id text primary key,
> category_id text not null
> );
>
> CREATE INDEX i_category ON test (category_id);
>
>
> I make 500k inserts in one transaction using a python script.
> For the random text I use random uuid from the function:
>
> uuid.uuid4()
>
> After those inserts I create another index:
>
> CREATE INDEX i_new ON test (category_id);
>
>
> select
> pg_size_pretty(pg_relation_size('i_category')),
> pg_size_pretty(pg_relation_size('i_new'))
> ;
>
> Results:
>
> '37 MB';'28 MB'

You didn't post your insertion script.  btree indexes are always going
to have extra space in them due to pages splitting and being only
partially filled -- insertion order over the range of your datum plays
into this (you'll get different index arrangements from random vs
ordered insertion).  for kicks, try reindexing both indexes and see
what the size is afterwords.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plpgsql; execute query inside exists

[Alban Hertroys]

On 17 October 2011 15:20, Merlin Moncure  wrote:
> A better way to do this is to query information_schema:
>
> PERFORM 1 FROM information_schema.tables where schema_name = x and
> table_name = y;
>
> IF FOUND THEN
>  CREATE TABLE ...
> END IF;
>
> (there is a race condition in the above code -- do you see it? if
> concurrent access to this function is an issue, you have to LOCK an
> object before running the PERFORM or perhaps use an advisory lock).

Is there? You'd think that with transactional DDL and the code running
in a single transaction (namely inside a stored function) it would be
concurrency-safe.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Mac OS X 10.6 - libpq.dylib vs. libpq.a and PQisthreadsafe()

[David McKeone]

Hello,

I've been getting acquainted with the C interface for libpq and have run into 
an issue with trying to link to the dynamic libpq while still getting thread 
safety.  I have tried the following by compiling the 9.1 source with the 
--enable-thread-safety flag and I've tried the libpq libraries provided in the 
EnterpriseDB build of 9.1.  All of this is on Mac OS X 10.6.8 with XCode 4.0.2.

The problem I'm having is that PQisthreadsafe() returns 0 when I link to the 
dynamic library(libpq.5.4.dylib), but it returns 1 when I link to the static 
library (libpq.a).  The code I'm using is as follows:

#include "libpq-fe.h"

int main (int argc, const char * argv[])
{
if (PQisthreadsafe() == 1) {
puts("Thread safe");
} else {
puts("Not thread safe");
}
}


Commands and output (main.c, libpq.a and libpq.5.4.dylib are all in the same 
directory):

gcc -I /Library/PostgreSQL/9.1/include main.c libpq.a -o main

Output: "Thread safe"

gcc -I /Library/PostgreSQL/9.1/include main.c libpq.5.4.dylib -o main

Output: "Not thread safe"


I'm admittedly not great with the nuances of the linker or with the PostgreSQL 
C interface so it's possible I'm missing something obvious here. If anyone 
knows how I should be using libpq as a dynamic library with thread safety, then 
that would be a great help.

Regards,
__
David McKeone
Arts Management Systems Ltd.
mailto:da...@artsman.com
http://www.artsman.com
Phone: (403) 536-1203 Fax: (403) 536-1210

Re: [GENERAL] plpgsql; execute query inside exists

[Merlin Moncure]

On Mon, Oct 17, 2011 at 8:44 AM, Alban Hertroys  wrote:
> On 17 October 2011 15:20, Merlin Moncure  wrote:
>> A better way to do this is to query information_schema:
>>
>> PERFORM 1 FROM information_schema.tables where schema_name = x and
>> table_name = y;
>>
>> IF FOUND THEN
>>  CREATE TABLE ...
>> END IF;
>>
>> (there is a race condition in the above code -- do you see it? if
>> concurrent access to this function is an issue, you have to LOCK an
>> object before running the PERFORM or perhaps use an advisory lock).
>
> Is there? You'd think that with transactional DDL and the code running
> in a single transaction (namely inside a stored function) it would be
> concurrency-safe.

Transactional DDL does not protect you from race conditions any more
than MVCC protects you from race conditions in regular DML.  What
transactional DDL does is roll back the changes in the event of an
error so you don't have half written schema changes in your database.
MVCC gives a rigorous definition of visibility rules and transactions
guarantee only a complete unit of work getting committed to the
database.  You still have to code defensively against multi-user
access however.  The good news is that multi user coding is about an
order of magnitude easier in sql (especially postgres variant) than in
any other development platform that I'm aware of.

The race condition here is basically the same problem that affects
'upsert' patterns:

test record(s) if found update if not found insert;

The problem comes that in between the test and the insert case someone
else can also test and get the insert in before you do.  You have two
general strategies to get around this: locking and retry.  I greatly
advise going the locking route unless your concurrency requirements
are very high.  It's much simpler, and since you're not invoking a
subtransaction, faster in the uncontested case.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [ADMIN] Trying to use binary replication - from tutorial

[Mark Keisler]

In your recovery.conf, you should also have a restore_command setting.
That's what the standby postgres will use to grab the archived WAL logs from
the master and it needs to.  So yes, you need archive set to on and an
archive_command command setting on the master.  You at least need that for
catching up from whatever happened between the pg_start_backup, rsync from
master to standby, then pg_stop_backup before it can start streaming
replication.  Those steps are in the tutorial you have mentioned :).




On Thu, Oct 13, 2011 at 3:31 PM, Evan Walter wrote:

> I pretty much didn't change anything in the config files except what was in
> the tutorial at
> http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial
>
> on the slave I created a recovery.conf file containing:
> standby_mode = 'on'
> primary_conninfo = 'host='
>
> on the master postgresql.conf I set
> listen_address = '*'
> wav_level = hot_standby
> max_wal_senders = 3
>
> I didn't change any of postgresql.conf Archiving settings.  They all seem
> to be commented out.  Could that be the problem?
>
> Evan
>
>
> On Thu, Oct 13, 2011 at 3:19 PM, Mark Keisler  wrote:
>
>> Do not rsync the pg_xlog.  Basically that error means that the
>> restore_command in your recovery.conf is not working.  You have hot_standby
>> archiving going on the master and a recovery_command on the slave, right?
>>
>>
>>
>> On Thu, Oct 13, 2011 at 10:41 AM, Evan Walter <
>> ewal...@decisionanalyst.com> wrote:
>>
>>>
>>>
>>> Hello,
>>> I am somewhat new with postgresql trying to find a good method of
>>> replication for my company.
>>> I am running through the tutorials on binary replication for postgresql
>>> 9.1.  Both servers are virtual box Ubuntu 10.10 on a laptop.
>>>
>>> I ran this rsync -av --exclude pg_xlog --exclude postgresql.conf
>>> /var/lib/postgresql/9.1/main/* postgres:>> standby>:/var/lib/postgresql/9.1/main/
>>>
>>> The standby server then will not restart.  It says it is missing
>>> pg_xlog/00 ...   files
>>>
>>> I ran the rsync again not excluding the pg_xlog.  This time both servers
>>> restart but there doesn't seem to be any replication occurring.
>>>
>>> I am curious why this is going on - why it didn't work excluding the
>>> pg_xlog.  Was there something wrong with my syntax?  Is there something I am
>>> missing not found in the tutorial?
>>> I will appreciate any help, advice.
>>>
>>> Thank you,
>>>
>>> Evan
>>>
>>
>>
>

Re: [GENERAL] Mac OS X 10.6 - libpq.dylib vs. libpq.a and PQisthreadsafe()

[Tom Lane]

David McKeone  writes:
> I've been getting acquainted with the C interface for libpq and have run into 
> an issue with trying to link to the dynamic libpq while still getting thread 
> safety.  I have tried the following by compiling the 9.1 source with the 
> --enable-thread-safety flag and I've tried the libpq libraries provided in 
> the EnterpriseDB build of 9.1.  All of this is on Mac OS X 10.6.8 with XCode 
> 4.0.2.

Hm, is there a libpq dylib in /usr/lib?  If so, maybe it's capturing the
reference?  "otool -L main" would be informative about which dylib is
actually getting called, I think.  If it's not what you expected, the
lack of a -L switch in your link command is probably the reason.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 9.1 got really fast ;)

[Steve Crawford]

On 10/16/2011 04:39 PM, Scott Marlowe wrote:

On Sun, Oct 16, 2011 at 5:24 PM, Tom Lane  wrote:

Scott Marlowe  writes:

On Sat, Oct 15, 2011 at 2:20 PM, Thomas Kellerer  wrote:

Total runtime: -2.368 ms<< this is amazing ;)

I get something similar when I do select now()-query_start from
pg_stat_activity on my Ubuntu 10.04 / pg 8.3 servers.

Within a transaction block that's not surprising, because now() is
defined as transaction start time not statement start time.

No transaction block.

Even stand-alone statements take place within a transaction - just not 
an explicit one.


Cheers,
Steve


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [ADMIN] Trying to use binary replication - from tutorial

[Evan Walter]

Yes thank you.
I actually did get it working eventually, with the things you mentioned.
Also, the recovery.conf has to be in the directory that contains the data
folder!  I previously had it in the folder with the config files, which in
my installation are different.  Once I moved the recovery.conf (this was
actually figured out by a colleague), the replication was working
beautifully.

Thanks
Evan Walter

On Mon, Oct 17, 2011 at 10:20 AM, Mark Keisler  wrote:

> In your recovery.conf, you should also have a restore_command setting.
> That's what the standby postgres will use to grab the archived WAL logs from
> the master and it needs to.  So yes, you need archive set to on and an
> archive_command command setting on the master.  You at least need that for
> catching up from whatever happened between the pg_start_backup, rsync from
> master to standby, then pg_stop_backup before it can start streaming
> replication.  Those steps are in the tutorial you have mentioned :).
>
>
>
>
>
> On Thu, Oct 13, 2011 at 3:31 PM, Evan Walter 
> wrote:
>
>> I pretty much didn't change anything in the config files except what was
>> in the tutorial at
>> http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial
>>
>> on the slave I created a recovery.conf file containing:
>> standby_mode = 'on'
>> primary_conninfo = 'host='
>>
>> on the master postgresql.conf I set
>> listen_address = '*'
>> wav_level = hot_standby
>> max_wal_senders = 3
>>
>> I didn't change any of postgresql.conf Archiving settings.  They all seem
>> to be commented out.  Could that be the problem?
>>
>> Evan
>>
>>
>> On Thu, Oct 13, 2011 at 3:19 PM, Mark Keisler wrote:
>>
>>> Do not rsync the pg_xlog.  Basically that error means that the
>>> restore_command in your recovery.conf is not working.  You have hot_standby
>>> archiving going on the master and a recovery_command on the slave, right?
>>>
>>>
>>>
>>> On Thu, Oct 13, 2011 at 10:41 AM, Evan Walter <
>>> ewal...@decisionanalyst.com> wrote:
>>>


 Hello,
 I am somewhat new with postgresql trying to find a good method of
 replication for my company.
 I am running through the tutorials on binary replication for postgresql
 9.1.  Both servers are virtual box Ubuntu 10.10 on a laptop.

 I ran this rsync -av --exclude pg_xlog --exclude postgresql.conf
 /var/lib/postgresql/9.1/main/* postgres:>>> standby>:/var/lib/postgresql/9.1/main/

 The standby server then will not restart.  It says it is missing
 pg_xlog/00 ...   files

 I ran the rsync again not excluding the pg_xlog.  This time both servers
 restart but there doesn't seem to be any replication occurring.

 I am curious why this is going on - why it didn't work excluding the
 pg_xlog.  Was there something wrong with my syntax?  Is there something I 
 am
 missing not found in the tutorial?
 I will appreciate any help, advice.

 Thank you,

 Evan

>>>
>>>
>>
>

Re: [GENERAL] plpgsql; execute query inside exists

[Alban Hertroys]

On 17 October 2011 16:24, Merlin Moncure  wrote:
> On Mon, Oct 17, 2011 at 8:44 AM, Alban Hertroys  wrote:
>> On 17 October 2011 15:20, Merlin Moncure  wrote:
>>> A better way to do this is to query information_schema:
>>>
>>> PERFORM 1 FROM information_schema.tables where schema_name = x and
>>> table_name = y;
>>>
>>> IF FOUND THEN
>>>  CREATE TABLE ...
>>> END IF;
>>>
>>> (there is a race condition in the above code -- do you see it? if
>>> concurrent access to this function is an issue, you have to LOCK an
>>> object before running the PERFORM or perhaps use an advisory lock).
>>
>> Is there? You'd think that with transactional DDL and the code running
>> in a single transaction (namely inside a stored function) it would be
>> concurrency-safe.
>
> Transactional DDL does not protect you from race conditions any more
> than MVCC protects you from race conditions in regular DML.  What
> transactional DDL does is roll back the changes in the event of an
> error so you don't have half written schema changes in your database.
> MVCC gives a rigorous definition of visibility rules and transactions
> guarantee only a complete unit of work getting committed to the
> database.  You still have to code defensively against multi-user
> access however.  The good news is that multi user coding is about an
> order of magnitude easier in sql (especially postgres variant) than in
> any other development platform that I'm aware of.
>
> The race condition here is basically the same problem that affects
> 'upsert' patterns:
>
> test record(s) if found update if not found insert;
>
> The problem comes that in between the test and the insert case someone
> else can also test and get the insert in before you do.  You have two
> general strategies to get around this: locking and retry.  I greatly
> advise going the locking route unless your concurrency requirements
> are very high.  It's much simpler, and since you're not invoking a
> subtransaction, faster in the uncontested case.

So what would happen if you don't lock? I think it's this:

 Session A  | Session B
+-
 SELECT x   | SELECT x
 NOT FOUND  | NOT FOUND
   ...  | CREATE TABLE
 CREATE TABLE   | 
 |
  |

If I understand correctly, if you don't mind the error and the
subsequent rollback in Session A, than there's not much need to lock,
or is there? It is important to be aware of the possible rollback of
such a transaction, of course.

And what would you lock? A record in information_schema.tables? That's
a read-only view. A table that doesn't exist yet? Can't do. A record
in the pg_ schema? Rather not...

I suppose you could work around that problem by keeping track of your
own tables that were generated using aforementioned plpgsql function.
Then you have a table (that you own) with records to lock.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 9.1 got really fast ;)

[Alban Hertroys]

On 17 October 2011 17:25, Steve Crawford  wrote:
> On 10/16/2011 04:39 PM, Scott Marlowe wrote:
>>
>> On Sun, Oct 16, 2011 at 5:24 PM, Tom Lane  wrote:
>>>
>>> Scott Marlowe  writes:

 On Sat, Oct 15, 2011 at 2:20 PM, Thomas Kellerer
  wrote:
>
> Total runtime: -2.368 ms<< this is amazing ;)

 I get something similar when I do select now()-query_start from
 pg_stat_activity on my Ubuntu 10.04 / pg 8.3 servers.
>>>
>>> Within a transaction block that's not surprising, because now() is
>>> defined as transaction start time not statement start time.
>>
>> No transaction block.
>>
> Even stand-alone statements take place within a transaction - just not an
> explicit one.

I doubt that more than 2.368 ms passed between the start of a
transaction and the stand-alone statement it's wrapping though. Not
impossible, but clock skew seems more likely to me.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plpgsql; execute query inside exists

[Merlin Moncure]

On Mon, Oct 17, 2011 at 10:28 AM, Alban Hertroys  wrote:
> On 17 October 2011 16:24, Merlin Moncure  wrote:
>> On Mon, Oct 17, 2011 at 8:44 AM, Alban Hertroys  wrote:
>>> On 17 October 2011 15:20, Merlin Moncure  wrote:
 A better way to do this is to query information_schema:

 PERFORM 1 FROM information_schema.tables where schema_name = x and
 table_name = y;

 IF FOUND THEN
  CREATE TABLE ...
 END IF;

 (there is a race condition in the above code -- do you see it? if
 concurrent access to this function is an issue, you have to LOCK an
 object before running the PERFORM or perhaps use an advisory lock).
>>>
>>> Is there? You'd think that with transactional DDL and the code running
>>> in a single transaction (namely inside a stored function) it would be
>>> concurrency-safe.
>>
>> Transactional DDL does not protect you from race conditions any more
>> than MVCC protects you from race conditions in regular DML.  What
>> transactional DDL does is roll back the changes in the event of an
>> error so you don't have half written schema changes in your database.
>> MVCC gives a rigorous definition of visibility rules and transactions
>> guarantee only a complete unit of work getting committed to the
>> database.  You still have to code defensively against multi-user
>> access however.  The good news is that multi user coding is about an
>> order of magnitude easier in sql (especially postgres variant) than in
>> any other development platform that I'm aware of.
>>
>> The race condition here is basically the same problem that affects
>> 'upsert' patterns:
>>
>> test record(s) if found update if not found insert;
>>
>> The problem comes that in between the test and the insert case someone
>> else can also test and get the insert in before you do.  You have two
>> general strategies to get around this: locking and retry.  I greatly
>> advise going the locking route unless your concurrency requirements
>> are very high.  It's much simpler, and since you're not invoking a
>> subtransaction, faster in the uncontested case.
>
> So what would happen if you don't lock? I think it's this:
>
>  Session A              | Session B
> +-
>  SELECT x               | SELECT x
>  NOT FOUND              | NOT FOUND
>   ...                  | CREATE TABLE
>  CREATE TABLE   | 
>                  |
>               |
>
> If I understand correctly, if you don't mind the error and the
> subsequent rollback in Session A, than there's not much need to lock,
> or is there? It is important to be aware of the possible rollback of
> such a transaction, of course.

right -- allowing for rollback and retrying (either from the client or
in the procedure via sub-transaction) is always an option.  I prefer
to lock -- it's faster (usually) and gives more regular behavior.

> And what would you lock? A record in information_schema.tables? That's
> a read-only view. A table that doesn't exist yet? Can't do. A record
> in the pg_ schema? Rather not...
> I suppose you could work around that problem by keeping track of your
> own tables that were generated using aforementioned plpgsql function.
> Then you have a table (that you own) with records to lock.

yeah -- you could use an advisory lock or a special table created for
that purpose, or a row of a table that does your tracking.  Agree that
locking system catalogs is *not* advisable.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 9.1 got really fast ;)

[Tom Lane]

Alban Hertroys  writes:
> On 17 October 2011 17:25, Steve Crawford  
> wrote:
>> Even stand-alone statements take place within a transaction - just not an
>> explicit one.

> I doubt that more than 2.368 ms passed between the start of a
> transaction and the stand-alone statement it's wrapping though. Not
> impossible, but clock skew seems more likely to me.

We take some pains to ensure that the same gettimeofday reading is used
for both a transaction's start timestamp and the statement timestamp of
its first statement.  So I'm not sure what's up with Scott's report.
But in the OP's EXPLAIN case, that's the difference between successive
readings taken within the EXPLAIN code, so it's hard to see how to
explain it in any other way than "your system clock went backwards".
Possibly the underlying cause is clock skew between different processors
on a multiprocessor machine?

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] index bloat question

[Szymon Guz]

On 17 October 2011 15:42, Merlin Moncure  wrote:

> On Mon, Oct 17, 2011 at 2:15 AM, Szymon Guz  wrote:
> >
> >
> > On 17 October 2011 02:01, Scott Marlowe  wrote:
> >>
> >> On Sun, Oct 16, 2011 at 2:41 PM, Szymon Guz  wrote:
> >> > Hi,
> >> > just a couple of questions:
> >> > will there be an index bloat if I have:
> >> > - a serial column and only add rows to the table?
> >> > - a text column and I only add rows to the table?
> >> > For the serial column the numbers are only incremented, for the text
> >> > column
> >> > I add random strings.
> >>
> >> With no deletes or updates, the only bloat will be from a non 100% fill
> >> factor.
> >
> > Hi Scott,
> >
> > if there is no bloat, how could you explain this:
> >
> > Simple test:
> >
> > CREATE TABLE test (
> > id text primary key,
> > category_id text not null
> > );
> >
> > CREATE INDEX i_category ON test (category_id);
> >
> >
> > I make 500k inserts in one transaction using a python script.
> > For the random text I use random uuid from the function:
> >
> > uuid.uuid4()
> >
> > After those inserts I create another index:
> >
> > CREATE INDEX i_new ON test (category_id);
> >
> >
> > select
> > pg_size_pretty(pg_relation_size('i_category')),
> > pg_size_pretty(pg_relation_size('i_new'))
> > ;
> >
> > Results:
> >
> > '37 MB';'28 MB'
>
> You didn't post your insertion script.  btree indexes are always going
> to have extra space in them due to pages splitting and being only
> partially filled -- insertion order over the range of your datum plays
> into this (you'll get different index arrangements from random vs
> ordered insertion).  for kicks, try reindexing both indexes and see
> what the size is afterwords.
>
> merlin
>


Yep, after reindexing the sizes are the same, I even get it why sizes were
different, thanks for the info.

regards
Szymon

Re: [GENERAL] 9.1 got really fast ;)

[Lincoln Yeoh]

At 11:44 PM 10/17/2011, Tom Lane wrote:

Alban Hertroys  writes:
> On 17 October 2011 17:25, Steve Crawford 
 wrote:

>> Even stand-alone statements take place within a transaction - just not an
>> explicit one.

> I doubt that more than 2.368 ms passed between the start of a
> transaction and the stand-alone statement it's wrapping though. Not
> impossible, but clock skew seems more likely to me.

We take some pains to ensure that the same gettimeofday reading is used
for both a transaction's start timestamp and the statement timestamp of
its first statement.  So I'm not sure what's up with Scott's report.
But in the OP's EXPLAIN case, that's the difference between successive
readings taken within the EXPLAIN code, so it's hard to see how to
explain it in any other way than "your system clock went backwards".
Possibly the underlying cause is clock skew between different processors
on a multiprocessor machine?


Some years ago the early Athlon Athlon X2 CPUs had unsynced TSCs and 
the OSes used the TSCs to speed up gettimeofday (somehow despite all 
the advances in CPUs, chipsets etc, billions of transistors the 
hardware bunch didn't help much for time keeping, yes there's HPET on 
some motherboards but HPET isn't that great either AFAIK ).


I suppose this might be due to a different but still similar issue.

References:
http://developer.amd.com/Membership/Print.aspx?ArticleID=38&web=http%3A%2F%2Fdeveloper.amd.com 


http://people.redhat.com/mingo/time-warp-test/time-warp-test.c

Regards,
Link.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Mac OS X 10.6 - libpq.dylib vs. libpq.a and PQisthreadsafe()

[David McKeone]

On 2011-10-17, at 4:23 PM, Tom Lane wrote:
> 
> Hm, is there a libpq dylib in /usr/lib?  If so, maybe it's capturing the
> reference?  "otool -L main" would be informative about which dylib is
> actually getting called, I think.  If it's not what you expected, the
> lack of a -L switch in your link command is probably the reason.
> 
>   regards, tom lane

Here is the output for otool -L for both.

Static libpq.a

main:
/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current 
version 125.2.11)


Dynamic libpq.5.4.dylib using -lpq

main:
libpq.5.dylib (compatibility version 5.0.0, current version 5.4.0)
/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current 
version 125.2.11)

Dynamic libpq.5.4.dylib using the command from the previous email  (This one is 
interesting because /usr/local/pgsql doesn't exist on this machine)

main:
/usr/local/pgsql/lib/libpq.5.dylib (compatibility version 5.0.0, 
current version 5.4.0)
/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current 
version 125.2.11)


I did have a libpq.5.dylib in /usr/lib and it turns out that that was the 
problem.  So it looks like, even though I was specifying the library, it just 
picked the one at /usr/lib version anyway.  (I obviously have much more to 
learn about how the linker works on OS X)

The solution was to move the Enterprise DB libpq.5.4.dylib into /usr/lib (and 
create associated symlinks) and it worked correctly after that.

Thanks for pointing me in the right direction.

[GENERAL] Use true and false when bulk-exporting boolean data

[Viktor Rosenfeld]

Hi,

I need to move data from PostgreSQL to MonetDB and also bulk-import data
into MonetDB that was bulk-exported from PostgreSQL by other people. My
problem is that boolean data is exported by PostgreSQL using the values
"t" and "f" (unquoted) and that MonetDB expects "true" and "false".  Is
there a way to change how boolean columns are exported?

I'm using the following command to export:

  \copy table to 'filename' WITH NULL 'NULL';

And to import (in MonetDB):

  copy into table from '/path/to/filename' USING DELIMITERS '\t','\n','';

Cheers,
Viktor

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 9.1 got really fast ;)

[Scott Marlowe]

On Mon, Oct 17, 2011 at 9:44 AM, Tom Lane  wrote:
> Alban Hertroys  writes:
>> On 17 October 2011 17:25, Steve Crawford  
>> wrote:
>>> Even stand-alone statements take place within a transaction - just not an
>>> explicit one.
>
>> I doubt that more than 2.368 ms passed between the start of a
>> transaction and the stand-alone statement it's wrapping though. Not
>> impossible, but clock skew seems more likely to me.
>
> We take some pains to ensure that the same gettimeofday reading is used
> for both a transaction's start timestamp and the statement timestamp of
> its first statement.  So I'm not sure what's up with Scott's report.
> But in the OP's EXPLAIN case, that's the difference between successive
> readings taken within the EXPLAIN code, so it's hard to see how to
> explain it in any other way than "your system clock went backwards".
> Possibly the underlying cause is clock skew between different processors
> on a multiprocessor machine?

Could be.  That machine has 48 AMD 61xx series cores in it.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Use true and false when bulk-exporting boolean data

[Henry Drexler]

couldn't you just wrap it in a case statement to change the t to true
etc...?

On Mon, Oct 17, 2011 at 2:29 PM, Viktor Rosenfeld  wrote:

> Hi,
>
> I need to move data from PostgreSQL to MonetDB and also bulk-import data
> into MonetDB that was bulk-exported from PostgreSQL by other people. My
> problem is that boolean data is exported by PostgreSQL using the values
> "t" and "f" (unquoted) and that MonetDB expects "true" and "false".  Is
> there a way to change how boolean columns are exported?
>
> I'm using the following command to export:
>
>  \copy table to 'filename' WITH NULL 'NULL';
>
> And to import (in MonetDB):
>
>  copy into table from '/path/to/filename' USING DELIMITERS '\t','\n','';
>
> Cheers,
> Viktor
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Use true and false when bulk-exporting boolean data

[Henry Drexler]

On Mon, Oct 17, 2011 at 3:11 PM, Henry Drexler  wrote:

> couldn't you just wrap it in a case statement to change the t to true
> etc...?
>
>
example:

select
case when (1=1) = true then 'true' else 'false' end

Re: [GENERAL] exclusive OR possible within a where clause?

[David Salisbury]

On 10/14/11 10:58 AM, David Fetter wrote:

On Thu, Oct 13, 2011 at 07:49:59PM -0400, Tom Lane wrote:

David Salisbury  writes:



Short version, is there a way to implement an exclusive OR in a where clause?


The boolean<>  operator will do the trick.

(x = y)<>  (a = b)

regards, tom lane


Factoring in NULLable columns, that's:

(x IS NOT DISTINCT FROM y)<>  (a IS NOT DISTINCT FROM b)

Cheers,
David.


Thanks for the replies!

I should note ( for the mail list archives I guess )
that the above suggestions don't work.  Both rows are
returned whether I use OR or <>, though maybe I'm not
understanding something.  I'm not sure why <> would work either,
as all I can find is <> is the same as !=, which is
different than the fabled XOR I was hoping for.  In fact
they would never equal.

But in the end it looks like wrapper sql around my output using
"select distinct.." should do the trick.

-Dave

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] exclusive OR possible within a where clause?

[John R Pierce]

On 10/17/11 12:15 PM, David Salisbury wrote:

<> is the same as !=, which is
different than the fabled XOR I was hoping for.  In fact
they would never equal. 


F != F -> false
F != T -> true
T != F -> true
T != T -> false


how is that different than XOR, assuming the arguments are booleans ?


--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] exclusive OR possible within a where clause?

[Ken Tanzer]

If you don't care about which row you get, how about adding a "LIMIT 1" to
your query?  Don't know if that counts as "messy" or not... :)

On Mon, Oct 17, 2011 at 12:15 PM, David Salisbury wrote:

>
>
> On 10/14/11 10:58 AM, David Fetter wrote:
>
>> On Thu, Oct 13, 2011 at 07:49:59PM -0400, Tom Lane wrote:
>>
>>> David Salisbury  writes:
>>>
>>
>  Short version, is there a way to implement an exclusive OR in a where
 clause?

>>>
>>> The boolean<>  operator will do the trick.
>>>
>>>(x = y)<>  (a = b)
>>>
>>>regards, tom lane
>>>
>>
>> Factoring in NULLable columns, that's:
>>
>> (x IS NOT DISTINCT FROM y)<>  (a IS NOT DISTINCT FROM b)
>>
>> Cheers,
>> David.
>>
>
> Thanks for the replies!
>
> I should note ( for the mail list archives I guess )
> that the above suggestions don't work.  Both rows are
> returned whether I use OR or <>, though maybe I'm not
> understanding something.  I'm not sure why <> would work either,
> as all I can find is <> is the same as !=, which is
> different than the fabled XOR I was hoping for.  In fact
> they would never equal.
>
> But in the end it looks like wrapper sql around my output using
> "select distinct.." should do the trick.
>
> -Dave
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general
>

Re: [GENERAL] exclusive OR possible within a where clause?

[David Salisbury]

On 10/17/11 1:19 PM, John R Pierce wrote:

On 10/17/11 12:15 PM, David Salisbury wrote:

<> is the same as !=, which is
different than the fabled XOR I was hoping for. In fact
they would never equal.


F != F -> false
F != T -> true
T != F -> true
T != T -> false


how is that different than XOR, assuming the arguments are booleans ?




Perhaps what I'm hoping to do got munged.  In essence it's equivalent of..

create table test ( something numeric );
insert into test values ( 1 );
insert into test values ( 2 );

select * from test where ( something = 1.5 + .5 ) or ( something = 1.5 - .5 );
 something
---
 1
 2
(2 rows)


select * from test where ( something = 1.5 + .5 ) <> ( something = 1.5 - .5 );
 something
---
 1
 2
(2 rows)

( which is of course equivalent of where something = 1 or something = 2 )


In my fabled XOR, I'd get the first one it matched, say something = 1, and the
something = 2 would then be ignored/dropped.

Dave





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] exclusive OR possible within a where clause?

[John R Pierce]

On 10/17/11 12:40 PM, David Salisbury wrote:

 something
---
 1
 2

select * from test where ( something = 1.5 + .5 ) .XOR. ( something = 
1.5 - .5 );



well,   something[1] = 1, so thats FALSE .XOR. TRUE, which is TRUE
and, something[2] = 2, so thats TRUE .XOR. FALSE, which is also TRUE

no?




--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] exclusive OR possible within a where clause?

[David Salisbury]

On 10/17/11 2:12 PM, John R Pierce wrote:

On 10/17/11 12:40 PM, David Salisbury wrote:

something
---
1
2

select * from test where ( something = 1.5 + .5 ) .XOR. ( something =
1.5 - .5 );



well, something[1] = 1, so thats FALSE .XOR. TRUE, which is TRUE
and, something[2] = 2, so thats TRUE .XOR. FALSE, which is also TRUE

no?



Ah yes.  I'm seeing the folly of my ways now (Doh!).

Distinct it is.

Thanks!

-ds

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] exclusive OR possible within a where clause?

[David Johnston]

-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of David Salisbury
Sent: Monday, October 17, 2011 3:41 PM
To: John R Pierce
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] exclusive OR possible within a where clause?

Perhaps what I'm hoping to do got munged.  In essence it's equivalent of..

create table test ( something numeric ); insert into test values ( 1 );
insert into test values ( 2 );

select * from test where ( something = 1.5 + .5 ) or ( something = 1.5 - .5
);
  something
---
  1
  2
(2 rows)


select * from test where ( something = 1.5 + .5 ) <> ( something = 1.5 - .5
);
  something
---
  1
  2
(2 rows)

( which is of course equivalent of where something = 1 or something = 2 )


In my fabled XOR, I'd get the first one it matched, say something = 1, and
the something = 2 would then be ignored/dropped.


-

1) There is no concept of "FIRST" since you failed to include an ORDER BY
clause

2) Given that "something" can only take on a single value comparing it
against two separate (and different) values renders the difference between
OR and XOR meaningless.

If you want good help you need to give good examples - preferably real ones.

What you are trying to do has NOTHING to do with XOR.  Provide a more
informative description of WHAT you are trying to do and additional REAL
sample data.  From what you've described here, though, you probably want to
use WINDOW functions (RANK/ROW_NUMBER) in a sub-query and then look for only
those rows with RANK/ROW_NUMBER equal to 1.

David J.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Video of Activity on PostgreSQL GIT repository

[Martijn van Oosterhout]

On Thu, Oct 13, 2011 at 11:55:47AM -0300, Fabrízio de Royes Mello wrote:
> Hi all,
> 
> I like to share this cool video which I build [1] (using gource [2]) to show
> the activity of PostgreSQL GIT repository in the few months ago.

That's pretty cool. I'm always astonished at the code turnover rate in
postgres, it's really a very active project.

> [1] http://www.youtube.com/watch?v=gzTBJW2EVJY
> [2] code.google.com/???p/???gource/
> [3] github.com/???postgres/???postgres

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
   -- Arthur Schopenhauer


signature.asc
Description: Digital signature

[GENERAL] Plans to fix table inheritance caveats

[Evan Martin]

Hi,

Are there any plans to fix the caveats documented in section 5.8.1, 
particularly allowing rows in a derived table to satisfy a foreign key 
defined on a base table? I know it's on the TODO list ( 
http://wiki.postgresql.org/wiki/Todo#Inheritance ) - just wondering if 
anyone is actually working on it or planning to in the foreseeable future.


It would certainly be my number one feature request for PostgreSQL! 
Table inheritance is very useful for persisting an object model with 
inheritance, but with this limitation it can effectively only be used 
when nothing references the base table (unless you give up referential 
integrity).


Regards,

Evan

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Out of Memory Error on Insert

[Mark Priest]

I am getting an Out of Memory error in my server connection process
while running a large insert query.

Postgres version: "PostgreSQL 8.2.16 on i686-pc-mingw32, compiled by
GCC gcc.exe (GCC) 3.4.2 (mingw-special)"
OS: Windows 7 Professional (v.6.1, build 7601 service pack 1)
The OS is 64 bit but the postgres app is a 32-bit app and I run out of
memory and the server process crashes as soon as I hit 2 GB of memory.
 I assume that is because that is the limit for 32-bit apps.
My client connection is via JDBC in case that is important.

I am creating two temporary tables as follows:

create temporary table simple_group  (groupId int8 not null, elementId
int8 not null, primary key (groupId, elementId))

create temporary table temp_usergroup_acl_entry  (elementId int8 not
null, userGroupId int8 not null, grantFlags int8 not null, denyflags
int8 not null, primary key (elementId, userGroupId))

Table simple_group has about 584 rows.  It represents the membership
of devices (elementId) in a group (groupId).  The crash happens when I
run the query to populate temp_usergroup_acl_entry.  The query is
below followed by the memory map information.  As you can see there
are a lot of full joins.

My goal with the query is to combine the bit maps of access rights
(stored in 8 byte ints) for lists of devices in various groups.  The
groups might have overlapping memberships so that is why I am using
the outer joins and the bit-wise or operator to combine the
permissions of the bit masks.  I know what the values of the bit-masks
should be for each group from some queries that run before this query.
 However, the previous queries do not eat up much memory at all.

Is there something I can do to prevent the out of memory error?  Or
perhaps there is a way I can re-write the query to achieve the same
result?

Insert query:


insert into temp_usergroup_acl_entry(elementId,userGroupId,grantFlags,denyflags)
select coalesce(q0.elementId, q1.elementId) as elementId,
coalesce(q0.userGroupId, q1.userGroupId) as userGroupId,
(coalesce(q0.grantFlags, 0) | coalesce(q1.grantFlags, 0)) as grantFlags,
(coalesce(q0.denyflags, 0) | coalesce(q1.denyflags, 0)) as denyflags from
(select coalesce(q2.elementId, q3.elementId) as elementId,
coalesce(q2.userGroupId, q3.userGroupId) as userGroupId,
(coalesce(q2.grantFlags, 0) | coalesce(q3.grantFlags, 0)) as grantFlags,
(coalesce(q2.denyflags, 0) | coalesce(q3.denyflags, 0)) as denyflags from
(select coalesce(q4.elementId, q5.elementId) as elementId,
coalesce(q4.userGroupId, q5.userGroupId) as userGroupId,
(coalesce(q4.grantFlags, 0) | coalesce(q5.grantFlags, 0)) as grantFlags,
(coalesce(q4.denyflags, 0) | coalesce(q5.denyflags, 0)) as denyflags from
(select coalesce(q6.elementId, q7.elementId) as elementId,
coalesce(q6.userGroupId, q7.userGroupId) as userGroupId,
(coalesce(q6.grantFlags, 0) | coalesce(q7.grantFlags, 0)) as grantFlags,
(coalesce(q6.denyflags, 0) | coalesce(q7.denyflags, 0)) as denyflags from
(select coalesce(q8.elementId, q9.elementId) as elementId,
coalesce(q8.userGroupId, q9.userGroupId) as userGroupId,
(coalesce(q8.grantFlags, 0) | coalesce(q9.grantFlags, 0)) as grantFlags,
(coalesce(q8.denyflags, 0) | coalesce(q9.denyflags, 0)) as denyflags from
(select coalesce(q10.elementId, q11.elementId) as elementId,
coalesce(q10.userGroupId, q11.userGroupId) as userGroupId,
(coalesce(q10.grantFlags, 0) | coalesce(q11.grantFlags, 0)) as grantFlags,
(coalesce(q10.denyflags, 0) | coalesce(q11.denyflags, 0)) as denyflags from
(select coalesce(q12.elementId, q13.elementId) as elementId,
coalesce(q12.userGroupId, q13.userGroupId) as userGroupId,
(coalesce(q12.grantFlags, 0) | coalesce(q13.grantFlags, 0)) as grantFlags,
(coalesce(q12.denyflags, 0) | coalesce(q13.denyflags, 0)) as denyflags from
(select coalesce(q14.elementId, q15.elementId) as elementId,
coalesce(q14.userGroupId, q15.userGroupId) as userGroupId,
(coalesce(q14.grantFlags, 0) | coalesce(q15.grantFlags, 0)) as grantFlags,
(coalesce(q14.denyflags, 0) | coalesce(q15.denyflags, 0)) as denyflags from
(select coalesce(q16.elementId, q17.elementId) as elementId,
coalesce(q16.userGroupId, q17.userGroupId) as userGroupId,
(coalesce(q16.grantFlags, 0) | coalesce(q17.grantFlags, 0)) as grantFlags,
(coalesce(q16.denyflags, 0) | coalesce(q17.denyflags, 0)) as denyflags from
(select coalesce(q18.elementId, q19.elementId) as elementId,
coalesce(q18.userGroupId, q19.userGroupId) as userGroupId,
(coalesce(q18.grantFlags, 0) | coalesce(q19.grantFlags, 0)) as grantFlags,
(coalesce(q18.denyflags, 0) | coalesce(q19.denyflags, 0)) as denyflags from
(select coalesce(q20.elementId, q21.elementId) as elementId,
coalesce(q20.userGroupId, q21.userGroupId) as userGroupId,
(coalesce(q20.grantFlags, 0) | coalesce(q21.grantFlags, 0)) as grantFlags,
(coalesce(q20.denyflags, 0) | coalesce(q21.denyflags, 0)) as denyflags from
(select coalesce(q22.elementId, q23.elementId) as elementId,
coalesce(q22.userGroupId, q23.userGroupId) as userGroupId,
(coalesce(q22.grantFlag