Re: [GENERAL] OT: Db2 connection pooling?
On Fri, Jan 15, 2010 at 5:36 PM, Joshua D. Drake wrote: > Mod_perl? That on our front-end servers, as well as just regular perl on the back end. -- “Don't eat anything you've ever seen advertised on TV” - Michael Pollan, author of "In Defense of Food" -- 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] OT: Db2 connection pooling?
On Fri, 2010-01-15 at 17:35 -0500, Alan McKay wrote: > On Fri, Jan 15, 2010 at 4:45 PM, Scott Marlowe > wrote: > > What language are you running this in again? There might be other > > options that are more language oriented (java for instance) than db > > oriented. Or maybe some intermediate layer for pooling that's db > > agnostic. > > Oh, should have mentioned that too - Perl Mod_perl? > > > -- > “Don't eat anything you've ever seen advertised on TV” > - Michael Pollan, author of "In Defense of Food" > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- 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] OT: Db2 connection pooling?
On Fri, Jan 15, 2010 at 4:45 PM, Scott Marlowe wrote: > What language are you running this in again? There might be other > options that are more language oriented (java for instance) than db > oriented. Or maybe some intermediate layer for pooling that's db > agnostic. Oh, should have mentioned that too - Perl -- “Don't eat anything you've ever seen advertised on TV” - Michael Pollan, author of "In Defense of Food" -- 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] OT: Db2 connection pooling?
On Fri, Jan 15, 2010 at 2:40 PM, Alan McKay wrote: >> http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/conn/c0006170.htm > > Yeah, that is Db2 Enterprise, and we have Workgroup Server version. > And the cost of upgrading to that was part of why we decided to move > to PG. > > So I should have been more specific - a FREE connection pooler :-) What language are you running this in again? There might be other options that are more language oriented (java for instance) than db oriented. Or maybe some intermediate layer for pooling that's db agnostic. -- 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] OT: Db2 connection pooling?
> http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/conn/c0006170.htm Yeah, that is Db2 Enterprise, and we have Workgroup Server version. And the cost of upgrading to that was part of why we decided to move to PG. So I should have been more specific - a FREE connection pooler :-) -- “Don't eat anything you've ever seen advertised on TV” - Michael Pollan, author of "In Defense of Food" -- 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] Incorrect FTS query results with GIN index
Thank you for the report, will see on this weekend Vyacheslav Kalinin wrote: Hello, Basically, I started testing prefix matching in FTS and got into troubles. Self-contained example follows: -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- 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] Calling a plpgsql function with composite type as parameter?
On Thu, Jan 14, 2010 at 8:50 PM, Jamie Begin wrote:
> I'm working on an e-commerce site that calls various plpgsql functions
> from a Python app. One of the things I need to do is create a
> shopping cart and add several items to it. I'd like for both of these
> steps to be contained within the same transaction so if an error
> occurs adding an item to the cart, the entire cart creation is rolled
> back. I'm attempting to use something like the code below (I've
> simplified it). However, a) I'm not sure if this is the correct
> architectural decision and b) I haven't been able to figure how how to
> call this function using a composite type (my "_cart_contents") as a
> parameter. I'd greatly appreciate any suggestions. Thanks!
>
>
> CREATE TABLE carts (id serial, cart_owner varchar, cart_name varchar);
> CREATE TABLE carts_items (id serial, cart_id int REFERENCES carts(id),
> product_name varchar, price decimal(5,2) );
>
> CREATE TYPE cart_item_type AS (product_name varchar, price decimal(5,2));
>
> CREATE OR REPLACE FUNCTION cart_create(
> _user_id int
> ,_cart_name varchar
> ,_cart_contents cart_item_type[]
> ) RETURNS bool AS $$
> DECLARE
> _cart_id int;
> _id int;
> _i int;
> _n varchar;
> _p decimal(5,2);
> _product_id int;
> BEGIN
>
> INSERT INTO carts (cart_owner, cart_name)
> VALUES (_user_id, _cart_name);
>
> SELECT id INTO _cart_id FROM carts WHERE id =
> CURRVAL('carts_id_seq');
>
> FOR _i IN COALESCE(array_lower(_cart_contents,1),0) ..
> COALESCE(array_upper(_cart_contents,1),-1) LOOP
> _n := _cart_contents[_i]['product_name'];
> _p := _cart_contents[_i]['price'];
> INSERT INTO cart_items (cart_id, product_name, price)
> VALUES (_cart_id, _n, _p);
> END LOOP;
>
> RETURN True;
>
> END; $$ LANGUAGE plpgsql;
you can built it via string. however, python has a really fabulous
driver (at least, on paper, I don't code python!) that supports arrays
and composites natively over the protocol. Take a look here:
http://python.projects.postgresql.org/docs/0.9/driver.html#type-support
And read the chapter carefully. If this fits your use case, it might
allow you to not build from string, which absolutely awful route if
you make heavy use of arrays/composites.
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Incorrect FTS query results with GIN index
Hello,
Basically, I started testing prefix matching in FTS and got into troubles.
Self-contained example follows:
postgres=# select version();
version
PostgreSQL 8.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-44), 32-bit
postgres=# create table test (id int, data text, tsvd tsvector);
postgres=# insert into test (id, data) values (1, 'hot stuff is here'), (2,
'light is hotter than dark'), (3, 'nothing is that hottie');
postgres=# update test set tsvd = to_tsvector('english', data);
postgres=# select * from test;
id | data| tsvd
+---+---
1 | hot stuff is here | 'hot':1 'stuff':2
2 | light is hotter than dark | 'dark':5 'hotter':3 'light':1
3 | nothing is that hottie| 'hotti':4 'noth':1
(3 rows)
Now let's play with queries:
postgres=# select * from test where tsvd @@ to_tsquery('english', 'hot');
id | data| tsvd
+---+---
1 | hot stuff is here | 'hot':1 'stuff':2
(1 row)
postgres=# select * from test where tsvd @@ to_tsquery('english', 'hot:*');
id | data| tsvd
+---+---
1 | hot stuff is here | 'hot':1 'stuff':2
2 | light is hotter than dark | 'dark':5 'hotter':3 'light':1
3 | nothing is that hottie| 'hotti':4 'noth':1
(3 rows)
postgres=# select * from test where tsvd @@ to_tsquery('english', 'hot:* |
hot');
id | data| tsvd
+---+---
1 | hot stuff is here | 'hot':1 'stuff':2
2 | light is hotter than dark | 'dark':5 'hotter':3 'light':1
3 | nothing is that hottie| 'hotti':4 'noth':1
(3 rows)
Looks good so far. Let's introduce an index:
postgres=# create index ix_test on test using gin(tsvd);
CREATE INDEX
postgres=# set enable_seqscan to off;
SET
First two queries result in the same row sets, but look at the third one:
postgres=# explain select * from test where tsvd @@
to_tsquery('english', 'hot:* | hot');
QUERY PLAN
--
Bitmap Heap Scan on test (cost=4.26..8.28 rows=1 width=68)
Recheck Cond: (tsvd @@ to_tsquery('english', 'hot:* | hot'::text))
-> Bitmap Index Scan on ix_test (cost=0.00..4.26 rows=1 width=0)
Index Cond: (tsvd @@ to_tsquery('english', 'hot:* | hot'::text))
postgres=# select * from test where tsvd @@ to_tsquery('english', 'hot:* |
hot');
id | data| tsvd
+---+---
1 | hot stuff is here | 'hot':1 'stuff':2
(1 row)
WTH? Apparently prefixed part of the query stopped working.
Interesting that the bug doesn't show up with GiST:
postgres=# drop index ix_test;
DROP INDEX
postgres=# create index ix_test on test using gist(tsvd);
CREATE INDEX
postgres=# select * from test where tsvd @@ to_tsquery('english', 'hot:* |
hot');
id | data| tsvd
+---+---
1 | hot stuff is here | 'hot':1 'stuff':2
2 | light is hotter than dark | 'dark':5 'hotter':3 'light':1
3 | nothing is that hottie| 'hotti':4 'noth':1
(3 rows)
Re: [GENERAL] Creation of tablespaces
Ubuntu never shipped with selinux, it is available by installing the "selinux" meta-package. Ubuntu does ship with AppArmor and loaded by default in Hardy 8.04 and beyond but I don't believe there are any PotgreSQL profiles. Aaron Thul http://www.chasingnuts.com On Fri, Jan 15, 2010 at 10:51 AM, Tom Lane wrote: > =?iso-8859-1?Q?DURAND_Beno=EEt?= writes: >> I work with Ubuntu 9.10 (upgraded from 8.04 LTS yesterday) and PostgreSQL >> 8.3. >> I can't create tablespaces. Pgsql seems to try changing access rights of the >> directory and fails to do it (permission denied), despite the directory is >> owned by the postgres user > > Are you sure that postgres has r+x rights on all the directories above > that one? > > If this were a Red Hat distro I would also wonder about selinux > permissions, but I don't know whether Ubuntu has selinux or enables > it by default. > > 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 > -- 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] Calling a plpgsql function with composite type as parameter?
Jamie Begin wrote:
I'm working on an e-commerce site that calls various plpgsql functions
from a Python app. One of the things I need to do is create a
shopping cart and add several items to it. I'd like for both of these
steps to be contained within the same transaction so if an error
occurs adding an item to the cart, the entire cart creation is rolled
back. I'm attempting to use something like the code below (I've
simplified it). However, a) I'm not sure if this is the correct
architectural decision and b) I haven't been able to figure how how to
call this function using a composite type (my "_cart_contents") as a
parameter. I'd greatly appreciate any suggestions. Thanks!
CREATE TABLE carts (id serial, cart_owner varchar, cart_name varchar);
CREATE TABLE carts_items (id serial, cart_id int REFERENCES carts(id),
product_name varchar, price decimal(5,2) );
CREATE TYPE cart_item_type AS (product_name varchar, price decimal(5,2));
CREATE OR REPLACE FUNCTION cart_create(
_user_id int
,_cart_name varchar
,_cart_contents cart_item_type[]
) RETURNS bool AS $$
DECLARE
_cart_id int;
_id int;
_i int;
_n varchar;
_p decimal(5,2);
_product_id int;
BEGIN
INSERT INTO carts (cart_owner, cart_name)
VALUES (_user_id, _cart_name);
SELECT id INTO _cart_id FROM carts WHERE id =
CURRVAL('carts_id_seq');
FOR _i IN COALESCE(array_lower(_cart_contents,1),0) ..
COALESCE(array_upper(_cart_contents,1),-1) LOOP
_n := _cart_contents[_i]['product_name'];
_p := _cart_contents[_i]['price'];
INSERT INTO cart_items (cart_id, product_name, price)
VALUES (_cart_id, _n, _p);
END LOOP;
RETURN True;
END; $$ LANGUAGE plpgsql;
You should probably have quantity in there also. But here's how you
would call the function:
SELECT cart_create(123, 'Scotts Cart',
array[('foo', 12.25),('bar', 13.99)]::_cart_item_type )
--
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] OT: Db2 connection pooling?
On Fri, 2010-01-15 at 12:16 -0500, Alan McKay wrote: > Hey folks, > > Sorry for the OT - we are most of the way through a Db2 --> PG > migration that is some 18 months in the making so far.We've got > maybe another 3 to 6 months to go before we are complete, and in the > meantime have identified the need for connection pooling in Db2, a-la > the excellent pgbouncer tool we have implemented on PG > > We are 100% CentOS based. > > Anyone know of anything? http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/conn/c0006170.htm > > From my process list it looks like Db2 V8.1 - my DBA is away at the > moment so I cannot ask him :) > > root 3370 1 0 2009 ?00:18:38 /opt/IBM/db2/V8.1/bin/db2fmcd > > thanks, > -Alan > > > -- > “Don't eat anything you've ever seen advertised on TV” > - Michael Pollan, author of "In Defense of Food" > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] OT: Db2 connection pooling?
Hey folks, Sorry for the OT - we are most of the way through a Db2 --> PG migration that is some 18 months in the making so far.We've got maybe another 3 to 6 months to go before we are complete, and in the meantime have identified the need for connection pooling in Db2, a-la the excellent pgbouncer tool we have implemented on PG We are 100% CentOS based. Anyone know of anything? From my process list it looks like Db2 V8.1 - my DBA is away at the moment so I cannot ask him :) root 3370 1 0 2009 ?00:18:38 /opt/IBM/db2/V8.1/bin/db2fmcd thanks, -Alan -- “Don't eat anything you've ever seen advertised on TV” - Michael Pollan, author of "In Defense of Food" -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: RE : [GENERAL] Creation of tablespaces
=?iso-8859-1?Q?DURAND_Beno=EEt?= writes: > The directory is on another (external) disk, mounted under /media. Postgres > has not r+x rights on that directory (or on /) which belong to root. That would be your problem then ... (It might not actually need r, but it definitely must have x, in order to follow that path at all.) 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] Creation of tablespaces
The directory is on another (external) disk, mounted under /media. Postgres has not r+x rights on that directory (or on /) which belong to root. regards, Benoit Durand De : Tom Lane [...@sss.pgh.pa.us] Date d'envoi : vendredi 15 janvier 2010 16:51 À : DURAND Benoît Cc : pgsql-general@postgresql.org Objet : Re: [GENERAL] Creation of tablespaces =?iso-8859-1?Q?DURAND_Beno=EEt?= writes: > I work with Ubuntu 9.10 (upgraded from 8.04 LTS yesterday) and PostgreSQL 8.3. > I can't create tablespaces. Pgsql seems to try changing access rights of the > directory and fails to do it (permission denied), despite the directory is > owned by the postgres user Are you sure that postgres has r+x rights on all the directories above that one? If this were a Red Hat distro I would also wonder about selinux permissions, but I don't know whether Ubuntu has selinux or enables it by default. 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] identify Tables without primary keys - postgres
Check http://petereisentraut.blogspot.com/2009/07/how-to-find-all-tables-without-primary.html On Fri, 2010-01-15 at 11:39 -0500, akp geek wrote: > Dear all - > > I have the following query to find the tables with > primary keys. can you please help me finding the tables without > primary key. > > select > distinct x.table_name, > from > information_schema.constraint_column_usage x, > pg_constraint b > where > b.contype='p' and > x.constraint_name=b.conname > > Regards >
[GENERAL] identify Tables without primary keys - postgres
Dear all - I have the following query to find the tables with primary keys. can you please help me finding the tables without primary key. select distinct x.table_name, from information_schema.constraint_column_usage x, pg_constraint b where b.contype='p' and x.constraint_name=b.conname Regards
[GENERAL] Tablespace creation
Hello, I work with Ubuntu 9.10 (upgraded from 8.04 LTS yesterday) and PostgreSQL 8.3. I can't create tablespaces. Pgsql seems to try changing access rights of the directory and fails to do it (permission denied), despite the directory is owned by the postgres user Under the Ubuntu 8.04 LTS distribution I had no problem with table spaces... Any idea ? bdur...@bdurand-desktop:/var/lib/postgresql/8.3$ sudo psql template1 -U postgres Mot de passe pour l'utilisateur postgres : Bienvenue dans psql 8.3.8, l'interface interactive de PostgreSQL. Saisissez: \copyright pour les termes de distribution \h pour l'aide-mémoire des commandes SQL \? pour l'aide-mémoire des commandes psql \g ou point-virgule en fin d'instruction pour exécuter la requête \q pour quitter template1=# create tablespace lacie owner bdurand location '/media/DD_LaCie/postgres/8.3'; ERREUR: n'a pas pu configurer les droits du répertoire « /media/DD_LaCie/postgres/8.3 » : Permission non accordée -- 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] Creation of tablespaces
=?iso-8859-1?Q?DURAND_Beno=EEt?= writes: > I work with Ubuntu 9.10 (upgraded from 8.04 LTS yesterday) and PostgreSQL 8.3. > I can't create tablespaces. Pgsql seems to try changing access rights of the > directory and fails to do it (permission denied), despite the directory is > owned by the postgres user Are you sure that postgres has r+x rights on all the directories above that one? If this were a Red Hat distro I would also wonder about selinux permissions, but I don't know whether Ubuntu has selinux or enables it by default. 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] Table name from directory
On Fri, Jan 15, 2010 at 7:59 AM, Jason Armstrong wrote: > How do I get the name of a database from a directory on disk (eg > $datadir/base/16494)? There's a handy little utility called oid2name you can use to do it from the command line. -- 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] vacuum issues under load?
On Fri, Jan 15, 2010 at 7:45 AM, Tom Lane wrote: > Ben Chobot writes: >> We have recently discovered a problem with our slony-1 cluster of 8.1.19 >> installs. Specifically, we are unable to vacuum a table on the master >> node; vacuum always hangs on the same index of the same table. If we do a >> slony switchover and make the other node the master, then *it* will become >> unable to vacuum that index. Vacuum on the slave always works quickly and >> without issue. Vacuum does not hang anywhere else. > >> When we tried to strace the vacuuming backend, it appeared as if it was >> trying to acquire a lock, but pg_lock showed nothing unexpected for that >> index. > > Try attaching to the process with gdb and getting a stack trace. > > Also ask on the slony lists if anyone's seen anything like this. I > don't know a reason for slony to have any effect like that, but there's > got to be *something* weird going on. I've seen a situation like there during ddl ops (using the slony execute command to run them on the cluster) where slony halts waiting on vacuum and everything else halts waiting on slony. That was with slony 1.2 With slony 2.0.3 or so, I had occasional complete lockups of my database that I didn't have time to troubleshoot as it was a live cluster and I had to restart slony and the databases to get them back up and running. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Creation of tablespaces
Hello, I work with Ubuntu 9.10 (upgraded from 8.04 LTS yesterday) and PostgreSQL 8.3. I can't create tablespaces. Pgsql seems to try changing access rights of the directory and fails to do it (permission denied), despite the directory is owned by the postgres user Under the Ubuntu 8.04 LTS distribution I had no problem with table spaces... Any idea ? bdur...@bdurand-desktop:/var/lib/postgresql/8.3$ sudo psql template1 -U postgres Mot de passe pour l'utilisateur postgres : Bienvenue dans psql 8.3.8, l'interface interactive de PostgreSQL. Saisissez: \copyright pour les termes de distribution \h pour l'aide-mémoire des commandes SQL \? pour l'aide-mémoire des commandes psql \g ou point-virgule en fin d'instruction pour exécuter la requête \q pour quitter template1=# create tablespace lacie owner bdurand location '/media/DD_LaCie/postgres/8.3'; ERREUR: n'a pas pu configurer les droits du répertoire « /media/DD_LaCie/postgres/8.3 » : Permission non accordée -- 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] vacuum issues under load?
Ben Chobot writes: > On Fri, 15 Jan 2010, Tom Lane wrote: >> OK, so it's trying to get an exclusive page-level lock on some page of >> the index. The only thing that could block that for any long period is >> if some other process is sitting with an open indexscan. Look around >> for processes that have been "idle in transaction" for a long time ... > There are some, but they've all been running less than a second. There's > no better place to look than pg_stat_activity, is there? Hmph. Given that, what we seem to be looking at is a leaked buffer pin count. There isn't any easy way to fix that except a database restart --- perhaps you can manage one over the weekend? I don't recall having heard of any such bugs lately, but 8.1.x is kind of an old branch. I'm still wondering if slony might've been involved somehow. 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] vacuum issues under load?
On Fri, 15 Jan 2010, Tom Lane wrote: #0 0x7f59a5d78ca7 in semop () from /lib/libc.so.6 #1 0x00546656 in PGSemaphoreLock () #2 0x005618ee in LockBufferForCleanup () #3 0x0045c8e2 in btbulkdelete () OK, so it's trying to get an exclusive page-level lock on some page of the index. The only thing that could block that for any long period is if some other process is sitting with an open indexscan. Look around for processes that have been "idle in transaction" for a long time ... There are some, but they've all been running less than a second. There's no better place to look than pg_stat_activity, is there? -- 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] Table name from directory
Jason Armstrong writes: > How do I get the name of a database from a directory on disk (eg > $datadir/base/16494)? That number should match the pg_database.oid column. 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] vacuum issues under load?
Ben Chobot writes: > On Fri, 15 Jan 2010, Tom Lane wrote: >> Try attaching to the process with gdb and getting a stack trace. > #0 0x7f59a5d78ca7 in semop () from /lib/libc.so.6 > #1 0x00546656 in PGSemaphoreLock () > #2 0x005618ee in LockBufferForCleanup () > #3 0x0045c8e2 in btbulkdelete () OK, so it's trying to get an exclusive page-level lock on some page of the index. The only thing that could block that for any long period is if some other process is sitting with an open indexscan. Look around for processes that have been "idle in transaction" for a long time ... 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
[GENERAL] Table name from directory
How do I get the name of a database from a directory on disk (eg $datadir/base/16494)? -- Jason Armstrong -- 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] vacuum issues under load?
On Fri, 15 Jan 2010, Tom Lane wrote: Ben Chobot writes: We have recently discovered a problem with our slony-1 cluster of 8.1.19 installs. Specifically, we are unable to vacuum a table on the master node; vacuum always hangs on the same index of the same table. If we do a slony switchover and make the other node the master, then *it* will become unable to vacuum that index. Vacuum on the slave always works quickly and without issue. Vacuum does not hang anywhere else. When we tried to strace the vacuuming backend, it appeared as if it was trying to acquire a lock, but pg_lock showed nothing unexpected for that index. Try attaching to the process with gdb and getting a stack trace. #0 0x7f59a5d78ca7 in semop () from /lib/libc.so.6 #1 0x00546656 in PGSemaphoreLock () #2 0x005618ee in LockBufferForCleanup () #3 0x0045c8e2 in btbulkdelete () #4 0x005eb179 in FunctionCall3 () #5 0x004ed81a in ?? () #6 0x004ee132 in lazy_vacuum_rel () #7 0x004ec0e4 in ?? () #8 0x004ecdeb in vacuum () #9 0x00577837 in ?? () #10 0x00578da1 in PortalRun () #11 0x00574b2b in ?? () #12 0x0057611e in PostgresMain () #13 0x0054e66b in ?? () #14 0x0054f5d1 in PostmasterMain () #15 0x005128de in main () -- 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] vacuum issues under load?
Ben Chobot writes: > We have recently discovered a problem with our slony-1 cluster of 8.1.19 > installs. Specifically, we are unable to vacuum a table on the master > node; vacuum always hangs on the same index of the same table. If we do a > slony switchover and make the other node the master, then *it* will become > unable to vacuum that index. Vacuum on the slave always works quickly and > without issue. Vacuum does not hang anywhere else. > When we tried to strace the vacuuming backend, it appeared as if it was > trying to acquire a lock, but pg_lock showed nothing unexpected for that > index. Try attaching to the process with gdb and getting a stack trace. Also ask on the slony lists if anyone's seen anything like this. I don't know a reason for slony to have any effect like that, but there's got to be *something* weird going on. 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: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour
2010/1/15 Vincenzo Romano : > 2010/1/15 Pavel Stehule : >> look on this page - maybe it could be useful for you >> http://wiki.postgresql.org/wiki/Sprintf > > This one could save my day! > Thanks Pavel. EXECUTE PRINTF( ); It's great. It's working. It's a must-have-as-builtin! -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS -- 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] Rows missing from table despite FK constraint
Guys, just following up on the issue... For no apparent reason the row became visible in seq scans. After another REINDEX everything appears to be correct. I don't know what the issue was and I'm rather uncomfortable about how it appeared and went away, but anyway it seems to have been resolved. Thank you all for help. -- Konrad Garus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour
2010/1/15 Pavel Stehule : > 2010/1/15 Vincenzo Romano : >> 2010/1/15 Pavel Stehule : 3. Then I need to add the TABLE-level CHECK condition in order to exploit the "constraint_exclusion = on": execute $l2$ alter table $l2$||ct||$l2$ add check( $l0$||co||$l0$ ) $l2$ using $l0$||va||$l0$; >>> >>> I am sorry, I am out. Your code isn't much readable: >>> >>> Minimally there isn't placeholder - some like $x >>> >>> It is game for the most cryptografic code :). >>> >>> You searching a functionality that isn't in pg now :(. Clause USING >>> doesn't work like macros or templates in C++ >>> >>> look on this page - maybe it could be useful for you >>> http://wiki.postgresql.org/wiki/Sprintf >>> >>> Regards >>> Pavel >>> >> >> Pavel, >> It's worse than cryptographic if you skip the starting points. I do >> admit it's far from plain PLPGSQL, though. >> The "co" text variable contains the $x placeholders, the "va" text >> variable contains the expressions to be used. > > this is bad. Placeholders are used only when are explicit - tj they > are in string constant. Content of variables are protected. sorry - it isn't exactly true. depend on nesting level. Pavel > > Pavel > >> >> -- >> Vincenzo Romano >> NotOrAnd Information Technologies >> cel. +39 339 8083886 | gtalk. vincenzo.rom...@notorand.it >> fix. +39 0823 454163 | skype. notorand.it >> fax. +39 02 700506964 | msn. notorand.it >> NON QVIETIS MARIBVS NAVTA PERITVS >> > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour
2010/1/15 Vincenzo Romano : > 2010/1/15 Pavel Stehule : >>> >>> 3. Then I need to add the TABLE-level CHECK condition in order to >>> exploit the "constraint_exclusion = on": >>> execute $l2$ >>> alter table $l2$||ct||$l2$ >>> add check( $l0$||co||$l0$ ) >>> $l2$ using $l0$||va||$l0$; >>> >> >> I am sorry, I am out. Your code isn't much readable: >> >> Minimally there isn't placeholder - some like $x >> >> It is game for the most cryptografic code :). >> >> You searching a functionality that isn't in pg now :(. Clause USING >> doesn't work like macros or templates in C++ >> >> look on this page - maybe it could be useful for you >> http://wiki.postgresql.org/wiki/Sprintf >> >> Regards >> Pavel >> > > Pavel, > It's worse than cryptographic if you skip the starting points. I do > admit it's far from plain PLPGSQL, though. > The "co" text variable contains the $x placeholders, the "va" text > variable contains the expressions to be used. this is bad. Placeholders are used only when are explicit - tj they are in string constant. Content of variables are protected. Pavel > > -- > Vincenzo Romano > NotOrAnd Information Technologies > cel. +39 339 8083886 | gtalk. vincenzo.rom...@notorand.it > fix. +39 0823 454163 | skype. notorand.it > fax. +39 02 700506964 | msn. notorand.it > NON QVIETIS MARIBVS NAVTA PERITVS > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour
2010/1/15 Pavel Stehule : > look on this page - maybe it could be useful for you > http://wiki.postgresql.org/wiki/Sprintf This one could save my day! Thanks Pavel. -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour
2010/1/15 Pavel Stehule : >> >> 3. Then I need to add the TABLE-level CHECK condition in order to >> exploit the "constraint_exclusion = on": >> execute $l2$ >> alter table $l2$||ct||$l2$ >> add check( $l0$||co||$l0$ ) >> $l2$ using $l0$||va||$l0$; >> > > I am sorry, I am out. Your code isn't much readable: > > Minimally there isn't placeholder - some like $x > > It is game for the most cryptografic code :). > > You searching a functionality that isn't in pg now :(. Clause USING > doesn't work like macros or templates in C++ > > look on this page - maybe it could be useful for you > http://wiki.postgresql.org/wiki/Sprintf > > Regards > Pavel > Pavel, It's worse than cryptographic if you skip the starting points. I do admit it's far from plain PLPGSQL, though. The "co" text variable contains the $x placeholders, the "va" text variable contains the expressions to be used. -- Vincenzo Romano NotOrAnd Information Technologies cel. +39 339 8083886 | gtalk. vincenzo.rom...@notorand.it fix. +39 0823 454163 | skype. notorand.it fax. +39 02 700506964 | msn. notorand.it NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour
> > 3. Then I need to add the TABLE-level CHECK condition in order to > exploit the "constraint_exclusion = on": > execute $l2$ > alter table $l2$||ct||$l2$ > add check( $l0$||co||$l0$ ) > $l2$ using $l0$||va||$l0$; > I am sorry, I am out. Your code isn't much readable: Minimally there isn't placeholder - some like $x It is game for the most cryptografic code :). You searching a functionality that isn't in pg now :(. Clause USING doesn't work like macros or templates in C++ look on this page - maybe it could be useful for you http://wiki.postgresql.org/wiki/Sprintf Regards Pavel -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
