Re: [GENERAL] PostgreSQL and XA Distributed Transaction Protocol
Christian Ferrari wrote: >>> Writing a specific stub to wrap-up PostgreSQL is not a too >>> difficult task, but I would be sure I am not re-inventing the wheel. >>> How can I am sure the standard XA interface is not availble? >>> Do you know if there is any document about this matter? > >> I searched the archives and found some confirmation that there is no such thing currently. >> I also couldn't find anything on PgFoundry. >> It might be a good idea to raise this on the pgsql-hackers list. >> I think that might be something good to add to the core distribution. >> At least it would be a good PgFoundry project! >> Yours, >> Laurenz Albe > > Dear all, > now the LIXA project (http://sourceforge.net/projects/lixa/) is stable enought to start the > development of the XA interface for PostgreSQL and I'm going to implement it for PostgreSQL 8.3 > Does anyone know if something has changed in the meantime? (Have PostgreSQL yet implemented the > standard native C XA library?). > > Any hints will be appreciated. First, that question should be asked on the -hackers mailing list. Don't start coding before your idea and design is approved there. Furthermore, any new patch must be developed against HEAD and not an old version of PostgreSQL. Reading http://wiki.postgresql.org/wiki/Submitting_a_Patch is a good starting point! Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Possible to replicate a single table with Pg 9.0.4?
Hi, Is it possible to replicate only a single or selected tables (as opposed to the whole shebang) using PG's built-in replication? I can't seem to find much on this topic, so I'm guessing not. I have a feeling I'll need to return to Londiste for this particular application. 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] Possible to replicate a single table with Pg 9.0.4?
On 05/09/2011 11:18 AM, Henry C. wrote: > Hi, > > Is it possible to replicate only a single or selected tables (as opposed to > the whole shebang) using PG's built-in replication? > No. > I can't seem to find much on this topic, so I'm guessing not. > > I have a feeling I'll need to return to Londiste for this particular > application. > Or Slony or Bucardo. But not PostgreSQL built-in replication. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Streaming replication info
Hi there, I would to configure a multinode PostgreSQL system using streaming replication. In my mind, I would have a base configuration with a master node and more than one slaves in streaming replication. I have no difficult to imagine start situation, but my problems come when master fails: if my slaves receive strems from the master, when this fails is there a manner to change connection_info in recovery.conf and change server to a new master? Is there a manner to reload recovery.conf without restart nodes? Or the only solution of this situation is that all slaves became "masters"? Thanks in advance. Regards Meph -- View this message in context: http://postgresql.1045698.n5.nabble.com/Streaming-replication-info-tp4381239p4381239.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Multiple table relationship constraints
On 5/5/2011 3:26 PM, Rick Genter wrote: Hm. I think the way I would handle this is to put the business logic for inserting/updating into the room_assignments table into one or more functions and have a special user that owns the tables and owns the functions and declare the functions to be SECURITY DEFINER. Revoke INSERT/UPDATE/DELETE access to the tables from all other users. Then you grant your regular users EXECUTE access to the functions. The functions run as the user that created them, so they will have direct INSERT/UPDATE/DELETE access to the tables while your regular users won't. Thanks everyone for your advice. I think this type of approach will be very helpful. -- Jack Christensen ja...@hylesanderson.edu -- 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] Streaming replication info
Hi, Il 09/05/11 09:18, mephysto ha scritto: I would to configure a multinode PostgreSQL system using streaming replication. In my mind, I would have a base configuration with a master node and more than one slaves in streaming replication. Cool. I have no difficult to imagine start situation, but my problems come when master fails: if my slaves receive strems from the master, when this fails is there a manner to change connection_info in recovery.conf and change server to a new master? I suggest that you look at repmgr, an open-source tool for managing replication the we are currently writing (http://projects.2ndquadrant.com/repmgr) which should make this kind of things much easier for you. Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.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] FILLFACTOR and increasing index
> > I have an index on a timestamp value that is inserted, for 90% > > of the inserts, in increasing order. No updates, no deletes on the > > table (appends only). > > The bit about "increasing order" is a red herring here. If you have > no updates, then you can leave the FILLFACTOR alone. > > FILLFACTOR controls how much extra room there is in the way the table > is stored, so that if a row is UPDATEd it might be possible to store > the row in the same disk page. This alleviates certain pathological > conditions with high-UPDATE tables and the way Postgres stores the > data (the non-overwriting storage manager). (please add the list when replying to emails) I'm talking about the index fillfactor, not the table fillfactor... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] stunnel with just postgresql client part
Hi My postgresql client (ejabberd postgresql lib) does not seem to be capable of ssl connection to postgresql server (with hostssl in pg_hba) So I tried to use run stunnel on the client box (ejabberd). It appears not to work. Here is stunnel log on the client end -- 2011.05.09 09:04:06 LOG7[7608:3086100176]: postgres accepted FD=7 from 127.0.0.1:41046 2011.05.09 09:04:06 LOG7[7608:3086097296]: postgres started 2011.05.09 09:04:06 LOG7[7608:3086097296]: FD 7 in non-blocking mode 2011.05.09 09:04:06 LOG7[7608:3086097296]: FD 8 in non-blocking mode 2011.05.09 09:04:06 LOG7[7608:3086097296]: FD 9 in non-blocking mode 2011.05.09 09:04:06 LOG7[7608:3086097296]: Connection from 127.0.0.1:41046 permitted by libwrap 2011.05.09 09:04:06 LOG5[7608:3086097296]: postgres connected from 127.0.0.1:41046 2011.05.09 09:04:06 LOG7[7608:3086097296]: FD 8 in non-blocking mode 2011.05.09 09:04:06 LOG7[7608:3086097296]: postgres connecting 10.10.10.10:5433 2011.05.09 09:04:06 LOG7[7608:3086097296]: connect_wait: waiting 10 seconds 2011.05.09 09:04:06 LOG7[7608:3086100176]: Cleaning up the signal pipe 2011.05.09 09:04:06 LOG6[7608:3086100176]: Child process 7614 finished with code 0 2011.05.09 09:04:06 LOG7[7608:3086097296]: connect_wait: connected 2011.05.09 09:04:06 LOG7[7608:3086097296]: Remote FD=8 initialized 2011.05.09 09:04:06 LOG7[7608:3086097296]: SSL state (connect): before/connect initialization 2011.05.09 09:04:06 LOG7[7608:3086097296]: SSL state (connect): SSLv3 write client hello A 2011.05.09 09:04:06 LOG3[7608:3086097296]: SSL_connect: Peer suddenly disconnected 2011.05.09 09:04:06 LOG5[7608:3086097296]: Connection reset: 0 bytes sent to SSL, 0 bytes sent to socket 2011.05.09 09:04:06 LOG7[7608:3086097296]: postgres finished (0 left) -- If required I can post postgresql server log. It seems to be shame that I have to run stunnel on the pg box as well. My question is that client only stunnel to pg server requiring ssl connection is not expected to work? Or am I doing something wrong? Thanks mr.wu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] simple update query too long
Hi list I use PostgreSQL 8.4.4. (with Postgis 1.4) I have a simple update query that takes hours to run. The table is rather big (2 millions records) but it takes more than 5 hours to run !! The query is just : *UPDATE grille SET inter = 0* The explain command seems ok : "Seq Scan on grille50 (cost=0.00..499813.56 rows=2125456 width=494)" The table as a geometry field geom (simple, it only stores squares) The table définition is : *CREATE TABLE grille50 ( id integer NOT NULL, geom geometry, inter integer DEFAULT 0, oc1 integer, oc2 integer, occalc integer, CONSTRAINT grille_pkey PRIMARY KEY (id), CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2), CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geom IS NULL), CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 2154) ) WITH ( OIDS=TRUE ); ALTER TABLE grille OWNER TO postgres; CREATE INDEX grille_geom ON grille USING gist (geom); CREATE INDEX grille_id ON grille USING btree (id);* So any ideas why is it soo long??? Many thanks Fabrice
Re: [GENERAL] FILLFACTOR and increasing index
On Mon, May 9, 2011 at 3:32 PM, Leonardo Francalanci wrote: >> > I have an index on a timestamp value that is inserted, for 90% >> > of the inserts, in increasing order. No updates, no deletes on the >> > table (appends only). >> >> The bit about "increasing order" is a red herring here. If you have >> no updates, then you can leave the FILLFACTOR alone. >> >> FILLFACTOR controls how much extra room there is in the way the table >> is stored, so that if a row is UPDATEd it might be possible to store >> the row in the same disk page. This alleviates certain pathological >> conditions with high-UPDATE tables and the way Postgres stores the >> data (the non-overwriting storage manager). > > > (please add the list when replying to emails) > > I'm talking about the index fillfactor, not the table fillfactor... It will be really useful to see some test results where you alter the fillfactor and report various measurables. -- 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] simple update query too long
On 05/09/2011 04:39 PM, F T wrote: > Hi list > > I use PostgreSQL 8.4.4. (with Postgis 1.4) > > I have a simple update query that takes hours to run. > The table is rather big (2 millions records) but it takes more than 5 hours > to run !! > > The query is just : > *UPDATE grille SET inter = 0* > > The explain command seems ok : > "Seq Scan on grille50 (cost=0.00..499813.56 rows=2125456 width=494)" > > The table as a geometry field geom (simple, it only stores squares) > The table définition is : > *CREATE TABLE grille50 > ( > id integer NOT NULL, > geom geometry, > inter integer DEFAULT 0, > oc1 integer, > oc2 integer, > occalc integer, > CONSTRAINT grille_pkey PRIMARY KEY (id), > CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2), > CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = > 'POLYGON'::text OR geom IS NULL), > CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 2154) > ) > WITH ( > OIDS=TRUE > ); > ALTER TABLE grille OWNER TO postgres; > CREATE INDEX grille_geom ON grille USING gist (geom); > CREATE INDEX grille_id ON grille USING btree (id);* > > > So any ideas why is it soo long??? > You've got three indexes, so you have the update on the table *and* the three indexes. Moreover, one of your indexes is a GiST with some PostGIS geometry. It takes usuaully quite some (long) time to update such index. How big is your table and each index? -- Guillaume http://www.postgresql.fr http://dalibo.com -- 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] FILLFACTOR and increasing index
> It will be really useful to see some test results where you alter the > fillfactor and report various measurables. It's not that easy... stressing "only" the index insertion speed won't be simple. I would have liked some "theory"... The docs seem to imply there are some guidelines, it's just that it's too cryptic: "for heavily updated tables a smaller fillfactor is better to minimize the need for page splits" "heavily updated" -> does it mean tables that are inserted/updated or only "updated"??? "leaf pages are filled to this percentage [...] when extending the index at the right (adding new largest key values)." Does it mean that since I will (almost) always add new largest key values, I should have a big or small FILLFACTOR??? I know that theory is one thing and real testing another; but I can't test everything; if there are some (proved?) guidelines I'd like to use them (example: I'm not going to test that fillfactor in table creation in my case won't make any difference in performance; I trust the docs and the fact that "it makes sense"). -- 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] simple update query too long
> On 05/09/2011 04:39 PM, F T wrote: >> Hi list >> >> I use PostgreSQL 8.4.4. (with Postgis 1.4) >> >> I have a simple update query that takes hours to run. >> The table is rather big (2 millions records) but it takes more than 5 >> hours >> to run !! >> >> The query is just : >> *UPDATE grille SET inter = 0* >> >> So any ideas why is it soo long??? >> > > You've got three indexes, so you have the update on the table *and* the > three indexes. Moreover, one of your indexes is a GiST with some PostGIS > geometry. It takes usuaully quite some (long) time to update such index. That only holds if the index needs to be updated. He's updating a column that is not indexed, so with a bit of luck the HOT might kick in. In that case the table would not bloat, the indexes would not need to be updated (and would no bloat) etc. The question is whether HOT may work in this particular case. > How big is your table and each index? I guess he mentioned there are 2 million rows, each about 500B wide (see the exlain posted before). That gives about 1GB of data, so with a bit of overhead I'd say about 1.5GB. Fabrice, have you done some monitoring (iostat, dstat, ...) when the update was in progress? I guess it's I/O boundd so I'd recommend to run this $ iostat -x 1 and see what is the utilization of the drives. regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ALTER TABLE ... DISABLE TRIGGERS Isolation leve
I'm using 8.4.1 I want to add a column to a table, but there are update triggers that will fire that don't need to fire for this operation. So, I'd like to add the column with triggers off. Normally this operation would take 10 or so seconds, so locking the table for that amount of time is not a big deal. I just want to make sure that no new data gets written to the table while the triggers are disabled. BEGIN; ALTER TABLE foo ADD COLUMN bar DISABLE TRIGGER USER; COMMIT; seems to leave the triggers disabled. My tests seem to show that BEGIN; ALTER TABLE foo DISABLE TRIGGER USER; locks the table fully, then ALTER TABLE foo ADD COLUMN bar; ALTER TABLE foo ENABLE TRIGGER USER; COMMIT; gets the job done. I only pause because I figured that the single DISABLE triggerin transaction would have flopped back when the transaction committed. I was wrong about that I only need a little bit of affirmation or a kick in the right direction. Thanks folks. -- 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] stunnel with just postgresql client part
On Mon, May 9, 2011 at 9:35 AM, zhong ming wu wrote: > Hi > > My postgresql client (ejabberd postgresql lib) does not seem to be > capable of ssl connection to postgresql server (with hostssl in > pg_hba) > > So I tried to use run stunnel on the client box (ejabberd). It > appears not to work. > > Here is stunnel log on the client end > -- > 2011.05.09 09:04:06 LOG7[7608:3086100176]: postgres accepted FD=7 from > 127.0.0.1:41046 > 2011.05.09 09:04:06 LOG7[7608:3086097296]: postgres started > 2011.05.09 09:04:06 LOG7[7608:3086097296]: FD 7 in non-blocking mode > 2011.05.09 09:04:06 LOG7[7608:3086097296]: FD 8 in non-blocking mode > 2011.05.09 09:04:06 LOG7[7608:3086097296]: FD 9 in non-blocking mode > 2011.05.09 09:04:06 LOG7[7608:3086097296]: Connection from > 127.0.0.1:41046 permitted by libwrap > 2011.05.09 09:04:06 LOG5[7608:3086097296]: postgres connected from > 127.0.0.1:41046 > 2011.05.09 09:04:06 LOG7[7608:3086097296]: FD 8 in non-blocking mode > 2011.05.09 09:04:06 LOG7[7608:3086097296]: postgres connecting > 10.10.10.10:5433 > 2011.05.09 09:04:06 LOG7[7608:3086097296]: connect_wait: waiting 10 seconds > 2011.05.09 09:04:06 LOG7[7608:3086100176]: Cleaning up the signal pipe > 2011.05.09 09:04:06 LOG6[7608:3086100176]: Child process 7614 finished > with code 0 > 2011.05.09 09:04:06 LOG7[7608:3086097296]: connect_wait: connected > 2011.05.09 09:04:06 LOG7[7608:3086097296]: Remote FD=8 initialized > 2011.05.09 09:04:06 LOG7[7608:3086097296]: SSL state (connect): > before/connect initialization > 2011.05.09 09:04:06 LOG7[7608:3086097296]: SSL state (connect): SSLv3 > write client hello A > 2011.05.09 09:04:06 LOG3[7608:3086097296]: SSL_connect: Peer suddenly > disconnected > 2011.05.09 09:04:06 LOG5[7608:3086097296]: Connection reset: 0 bytes > sent to SSL, 0 bytes sent to socket > 2011.05.09 09:04:06 LOG7[7608:3086097296]: postgres finished (0 left) > -- > > If required I can post postgresql server log. > > It seems to be shame that I have to run stunnel on the pg box as well. > > My question is that client only stunnel to pg server requiring ssl > connection is not expected to work? Or am I doing something wrong? what version stunnel? did you set the protocol in stunnel.conf? 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] ALTER TABLE ... DISABLE TRIGGERS Isolation leve
CG writes: > I want to add a column to a table, but there are update triggers that will > fire that don't need to fire for this operation. So, I'd like to add the > column with triggers off. Normally this operation would take 10 or so > seconds, so locking the table for that amount of time is not a big deal. I > just want to make sure that no new data gets written to the table while the > triggers are disabled. Are you overthinking the problem? Adding a column without a default doesn't do any row updates and shouldn't fire any triggers. I'm not sure that adding a column *with* a default will fire update triggers either. (That might be a bug, if so ...) Suggest testing before assuming you have a problem to solve. 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 as parameter
All, I have a function that takes the table name the parameter. After some digging I found that this can be made possible by have the query as a string and EXECUTE it. EXECUTE 'SELECT * FROM "' || table || '" WHERE '; The above works. But I want the result in a record variable for further processing. So my query actually is EXECUTE 'SELECT * FROM "' || table || '" INTO "record_data" WHERE '; This one will not work with the following error message: ERROR: syntax error at or near "INTO" Can some one help me ? Thanks, Sairam Krishnamurthy +1 612 859 8161
Re: [GENERAL] simple update query too long
On Mon, May 9, 2011 at 10:29 AM, wrote: >> On 05/09/2011 04:39 PM, F T wrote: >>> Hi list >>> >>> I use PostgreSQL 8.4.4. (with Postgis 1.4) >>> >>> I have a simple update query that takes hours to run. >>> The table is rather big (2 millions records) but it takes more than 5 >>> hours >>> to run !! >>> >>> The query is just : >>> *UPDATE grille SET inter = 0* >>> > >>> So any ideas why is it soo long??? >>> >> >> You've got three indexes, so you have the update on the table *and* the >> three indexes. Moreover, one of your indexes is a GiST with some PostGIS >> geometry. It takes usuaully quite some (long) time to update such index. > > That only holds if the index needs to be updated. He's updating a column > that is not indexed, so with a bit of luck the HOT might kick in. In that > case the table would not bloat, the indexes would not need to be updated > (and would no bloat) etc. > > The question is whether HOT may work in this particular case. HOT unfortunately does not provide a whole lot of benefit for this case. HOT like brief, small transactions to the in page cleanup work can be done as early as possible. The nature of postgres is such that you want to do everything you can to avoid table wide updates (up to and including building a new table instead). 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] stunnel with just postgresql client part
On Mon, May 9, 2011 at 2:01 PM, Merlin Moncure wrote: . . . >> It seems to be shame that I have to run stunnel on the pg box as well. >> >> My question is that client only stunnel to pg server requiring ssl >> connection is not expected to work? Or am I doing something wrong? > > what version stunnel? did you set the protocol in stunnel.conf? > stunnel-4.15-2.el5.1 I was not setting protocol. But since I got your message, I tried 'protocol = pgsql' in stunnel.conf Still no go.. In stunnel log, there is now new part about 'protocol pgsql not supported in client mode' 2011.05.09 16:20:48 LOG7[8758:3086231248]: postgres accepted FD=7 from 127.0.0.1:50693 2011.05.09 16:20:48 LOG7[8758:3086228368]: postgres started 2011.05.09 16:20:48 LOG7[8758:3086228368]: FD 7 in non-blocking mode 2011.05.09 16:20:48 LOG7[8758:3086228368]: FD 8 in non-blocking mode 2011.05.09 16:20:48 LOG7[8758:3086228368]: FD 9 in non-blocking mode 2011.05.09 16:20:48 LOG7[8758:3086231248]: Cleaning up the signal pipe 2011.05.09 16:20:48 LOG6[8758:3086231248]: Child process 8761 finished with code 0 2011.05.09 16:20:48 LOG7[8758:3086228368]: Connection from 127.0.0.1:50693 permitted by libwrap 2011.05.09 16:20:48 LOG5[8758:3086228368]: postgres connected from 127.0.0.1:50693 2011.05.09 16:20:48 LOG7[8758:3086228368]: FD 8 in non-blocking mode 2011.05.09 16:20:48 LOG7[8758:3086228368]: postgres connecting 10.10.10.10:5433 2011.05.09 16:20:48 LOG7[8758:3086228368]: connect_wait: waiting 10 seconds 2011.05.09 16:20:48 LOG7[8758:3086228368]: connect_wait: connected 2011.05.09 16:20:48 LOG7[8758:3086228368]: Remote FD=8 initialized 2011.05.09 16:20:48 LOG5[8758:3086228368]: Negotiations for pgsql (client side) started 2011.05.09 16:20:48 LOG3[8758:3086228368]: Protocol pgsql not supported in client mode 2011.05.09 16:20:48 LOG5[8758:3086228368]: Connection reset: 0 bytes sent to SSL, 0 bytes sent to socket 2011.05.09 16:20:48 LOG7[8758:3086228368]: postgres finished (0 left) --- postgres server log LOG: could not receive data from client: Connection reset by peer LOG: incomplete startup packet - output from psql psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. -- 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] stunnel with just postgresql client part
On Mon, May 9, 2011 at 3:24 PM, zhong ming wu wrote: > On Mon, May 9, 2011 at 2:01 PM, Merlin Moncure wrote: > . > . > . >>> It seems to be shame that I have to run stunnel on the pg box as well. >>> >>> My question is that client only stunnel to pg server requiring ssl >>> connection is not expected to work? Or am I doing something wrong? >> >> what version stunnel? did you set the protocol in stunnel.conf? >> > > > stunnel-4.15-2.el5.1 > > I was not setting protocol. But since I got your message, I tried > 'protocol = pgsql' in stunnel.conf see: http://pgbouncer.projects.postgresql.org/doc/faq.html#_how_to_use_ssl_connections_with_pgbouncer "Use Stunnel. Since version 4.27 it supports PostgreSQL protocol for both client and server side. It is activated by setting protocol=pgsql. For older 4.2x versions the support code is available as patch: stunnel-postgres.diff Alternative is to use Stunnel on both sides of connection, then the protocol support is not needed." 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] Table name as parameter
On 05/09/2011 12:33 PM, Sairam Krishnamurthy wrote: All, I have a function that takes the table name the parameter. After some digging I found that this can be made possible by have the query as a string and EXECUTE it. EXECUTE 'SELECT * FROM "' || table || '" WHERE '; The above works. But I want the result in a record variable for further processing. So my query actually is EXECUTE 'SELECT * FROM "' || table || '" INTO "record_data" WHERE '; Try.: EXECUTE 'SELECT * FROM "' || table || '" WHERE ' INTO record_data; This one will not work with the following error message: ERROR: syntax error at or near "INTO" Can some one help me ? Thanks, Sairam Krishnamurthy +1 612 859 8161 -- Adrian Klaver adrian.kla...@gmail.com -- 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] FILLFACTOR and increasing index
Dne 9.5.2011 17:25, Leonardo Francalanci napsal(a): >> It will be really useful to see some test results where you alter the >> fillfactor and report various measurables. > > > It's not that easy... stressing "only" the index insertion > speed won't be simple. I would have liked some "theory"... > The docs seem to imply there are some guidelines, it's > just that it's too cryptic: > > "for heavily updated tables a smaller fillfactor is better > to minimize the need for page splits" > > > "heavily updated" -> does it mean tables that are inserted/updated > or only "updated"??? Well, an UPDATE is actually DELETE+INSERT (that's how PostgreSQL MVCC works). It may be a bit more complicated with HOT, but that's not your case, as you're only inserting data. > "leaf pages are filled to this percentage [...] when extending the index > at the right (adding new largest key values)." Hmmm, not sure how exactly this works, but I guess that if you're only inserting data then fillfactor=100 is the right thing. I believe it kicks in only when you need to insert data into an 'old' leaf page. If the page is full, then it needs to be split but if you reserve some free space (using e.g. fillfactor=80) then the split is not needed. > Does it mean that since I will (almost) always add new largest key > values, I should have a big or small FILLFACTOR??? I'd go with the fillfactor=100. > I know that theory is one thing and real testing another; but I can't > test everything; if there are some (proved?) guidelines I'd like to > use them (example: I'm not going to test that fillfactor in table creation > in my case won't make any difference in performance; I trust the > docs and the fact that "it makes sense"). Yes, I use the same approach, but I'm not aware of any such guideline related to fillfactor with indexes. Anyway those guidelines need to be written by someone, so you have a great opportunity ;-) regards Tomas -- 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] FILLFACTOR and increasing index
Dne 9.5.2011 17:25, Leonardo Francalanci napsal(a): > I know that theory is one thing and real testing another; but I can't > test everything; if there are some (proved?) guidelines I'd like to > use them (example: I'm not going to test that fillfactor in table creation > in my case won't make any difference in performance; I trust the > docs and the fact that "it makes sense"). > Anyway testing this (with the 'insert only' workload) may be quite simple: = fillfactor = 100 testdb=# create table test_fill (id int); CREATE TABLE Time: 2,515 ms testdb=# create index test_fill_idx on test_fill(id) with (fillfactor=100); CREATE INDEX Time: 10,331 ms testdb=# insert into test_fill select i from generate_series(1,100) s(i); INSERT 0 100 Time: 11542,512 ms testdb=# select relpages from pg_class where relname = 'test_fill_idx'; relpages -- 1977 (1 row) fillfactor = 70 testdb=# create table test_fill (id int); CREATE TABLE Time: 1,382 ms testdb=# create index test_fill_idx on test_fill(id) with (fillfactor=70); CREATE INDEX Time: 10,296 ms testdb=# insert into test_fill select i from generate_series(1,100) s(i); INSERT 0 100 Time: 7,398 ms testdb=# select relpages from pg_class where relname = 'test_fill_idx'; relpages -- 2819 (1 row) So there seems to be no difference in insert performance (the INSERT takes about 11s in both cases), but the size of the index with fillfactor=70 needs much more space. So with the insert only (in ascending order) workload, I'd go with fillfactor=100 (or you may leave it at 90, which is the default value, the difference will be negligible). regards Tomas -- 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] stunnel with just postgresql client part
On Mon, May 9, 2011 at 4:37 PM, Merlin Moncure wrote: >> I was not setting protocol. But since I got your message, I tried >> 'protocol = pgsql' in stunnel.conf > > see: > http://pgbouncer.projects.postgresql.org/doc/faq.html#_how_to_use_ssl_connections_with_pgbouncer > > "Use Stunnel. Since version 4.27 it supports PostgreSQL protocol for > both client and server side. It is activated by setting > protocol=pgsql. > > For older 4.2x versions the support code is available as patch: > stunnel-postgres.diff > > Alternative is to use Stunnel on both sides of connection, then the > protocol support is not needed." > Thanks. Yes, when I installed the latest stunnel-4.36 it works. One strange thing I notice. When I do ssl connect with psql I am supposed to get a message like SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) With client side stunnel and (nonssl capable) psql I am not getting this message. But still the connection seems to be ssl.. -- 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] stunnel with just postgresql client part
On Mon, May 9, 2011 at 5:03 PM, zhong ming wu wrote: > On Mon, May 9, 2011 at 4:37 PM, Merlin Moncure wrote: >>> I was not setting protocol. But since I got your message, I tried >>> 'protocol = pgsql' in stunnel.conf >> >> see: >> http://pgbouncer.projects.postgresql.org/doc/faq.html#_how_to_use_ssl_connections_with_pgbouncer >> >> "Use Stunnel. Since version 4.27 it supports PostgreSQL protocol for >> both client and server side. It is activated by setting >> protocol=pgsql. >> >> For older 4.2x versions the support code is available as patch: >> stunnel-postgres.diff >> >> Alternative is to use Stunnel on both sides of connection, then the >> protocol support is not needed." >> > > > Thanks. Yes, when I installed the latest stunnel-4.36 it works. > > One strange thing I notice. When I do ssl connect with psql I am > supposed to get a message like > > SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) > > With client side stunnel and (nonssl capable) psql I am not getting > this message. But still the connection seems to be ssl.. it is? try setting up your connection string to require ssl. 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] stunnel with just postgresql client part
On Mon, May 9, 2011 at 6:42 PM, Merlin Moncure wrote: >> Thanks. Yes, when I installed the latest stunnel-4.36 it works. >> >> One strange thing I notice. When I do ssl connect with psql I am >> supposed to get a message like >> >> SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) >> >> With client side stunnel and (nonssl capable) psql I am not getting >> this message. But still the connection seems to be ssl.. > > it is? try setting up your connection string to require ssl. > I assume it is because in pg_hba.conf "hostssl" is specified for this client ip/user/database. Plus I check ps output on the server during the connection and postgres server reports that connection is from the ip address specified in pg_hba.conf Here is what I tried --- PGSSLMODE=require bin/psql -h 127.0.0.1 -U xmpp xmpp psql: server does not support SSL, but SSL was required -- Just so I don't get confused between multiple lines in pg_hba.conf I also deleted all other lines in it and retested. Assuming postgres server is correctly applying the restrictions in pg_hba.conf, and assuming the out put of "ps" is reliable then I am doing an ssl connection but somehow psql does not think so and does not work unless I drop PGSSLMODE=require -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_upgrade only to 9.0 ?
Hi, I'm currently running 8.4.4. I downloaded the source for 9.0.4 and installed it, and then installed pg_upgrade and ran it, and got the following message: > This utility can only upgrade to PostgreSQL version 9.0. It seems strange to me that it can only upgrade to that and not 4 patch points above, but still, so I go to the source directory to download 9.0 and it isn't listed. Could anyone tell me how I'm supposed to get this to work please? I'd be really grateful. Regards, Iain -- 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] pg_upgrade only to 9.0 ?
On Mon, May 9, 2011 at 6:10 PM, Iain Barnett wrote: > Hi, > > I'm currently running 8.4.4. I downloaded the source for 9.0.4 and installed > it, and then installed pg_upgrade and ran it, and got the following message: > >> This utility can only upgrade to PostgreSQL version 9.0. > > It seems strange to me that it can only upgrade to that and not 4 patch > points above, but still, so I go to the source directory to download 9.0 and > it isn't listed. > > Could anyone tell me how I'm supposed to get this to work please? I'd be > really grateful. How did you run it? i.e. what exactly did you type in to run 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] pg_upgrade only to 9.0 ?
On 10 May 2011, at 01:16, Scott Marlowe wrote: > On Mon, May 9, 2011 at 6:10 PM, Iain Barnett wrote: >> Hi, >> >> I'm currently running 8.4.4. I downloaded the source for 9.0.4 and installed >> it, and then installed pg_upgrade and ran it, and got the following message: >> >>> This utility can only upgrade to PostgreSQL version 9.0. >> >> It seems strange to me that it can only upgrade to that and not 4 patch >> points above, but still, so I go to the source directory to download 9.0 and >> it isn't listed. >> >> Could anyone tell me how I'm supposed to get this to work please? I'd be >> really grateful. > > How did you run it? i.e. what exactly did you type in to run it? Sorry, ignore me, I wasn't following the instructions given here properly. http://developer.postgresql.org/pgdocs/postgres/pgupgrade.html Feel free to give me a slap via email for skimming documents and asking stupid questions! Thanks for making the effort though. Regards, Iain -- 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] stunnel with just postgresql client part
On Mon, May 9, 2011 at 7:17 PM, zhong ming wu wrote: > On Mon, May 9, 2011 at 6:42 PM, Merlin Moncure wrote: >>> Thanks. Yes, when I installed the latest stunnel-4.36 it works. >>> >>> One strange thing I notice. When I do ssl connect with psql I am >>> supposed to get a message like >>> >>> SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) >>> >>> With client side stunnel and (nonssl capable) psql I am not getting >>> this message. But still the connection seems to be ssl.. >> >> it is? try setting up your connection string to require ssl. >> > > > I assume it is because in pg_hba.conf "hostssl" is specified for this > client ip/user/database. Plus I check ps output on the server during > the connection and postgres server reports that connection is from the > ip address specified in pg_hba.conf > > Here is what I tried > --- > PGSSLMODE=require bin/psql -h 127.0.0.1 -U xmpp xmpp > psql: server does not support SSL, but SSL was required > -- > > Just so I don't get confused between multiple lines in pg_hba.conf I > also deleted all other lines in it and retested. Assuming postgres > server is correctly applying the restrictions in pg_hba.conf, and > assuming the out put of "ps" is reliable then I am doing an ssl > connection but somehow psql does not think so and does not work unless > I drop PGSSLMODE=require Now manybe *I'm* a little confused. Are you connecting to the write port (stunnel's secure port)? As I understand it, the stunnel pgsql protocol is such that the client side libpq application can connect to stunnel which unwraps the encrypted data and connects w/o ssl to postgres. From the server's point of view, the connection should be unencrypted and from the client's it should remain encrypted. I can think of two reasons why you would want to do this: *) pgbouncer, or a some other connection pooler type piece of software that does not support ssl *) for loading purposes you are trying to keep all encryption/decryption off the main server. 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] "interval hour to minute" or "interval day to minute"
On Sun, Apr 17, 2011 at 04:55:51PM +0100, Jack Douglas wrote: > I discovered the 'fields' option of 'interval', but i can't figure out > from the docs how it is supposed to work. Are "hour to minute" and "day > to minute" really the same thing? And if not, in what circumstances are > they treated differently? As of version 8.4, they behave identically. The code has this comment, some form of which probably belongs in the documentation: /* * Our interpretation of intervals with a limited set of fields is * that fields to the right of the last one specified are zeroed out, * but those to the left of it remain valid. Thus for example there * is no operational difference between INTERVAL YEAR TO MONTH and * INTERVAL MONTH. In some cases we could meaningfully enforce that * higher-order fields are zero; for example INTERVAL DAY could reject * nonzero "month" field. However that seems a bit pointless when we * can't do it consistently. (We cannot enforce a range limit on the * highest expected field, since we do not have any equivalent of * SQL's .) * * Note: before PG 8.4 we interpreted a limited set of fields as * actually causing a "modulo" operation on a given value, potentially * losing high-order as well as low-order information. But there is * no support for such behavior in the standard, and it seems fairly * undesirable on data consistency grounds anyway. Now we only * perform truncation or rounding of low-order fields. */ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
