[GENERAL] Getting "getsockopt(TCP_KEEPALIVE) failed" LOG message in PG Logs on Solaris 10

Hi, I am getting "getsockopt(TCP_KEEPALIVE) failed: Option not supported by protocol" log message in the PG Logs whenever I run a query referencing "pg_catalog.pg_settings" on Solaris 10 (both Sprac and x86-64). You can reproduce this case by running a query like: SELECT name, setting, unit FROM

[GENERAL] Incorrect index being used

Hi PostgreSQL community, I have the following query, run immediately after executing VACUUM in the database. There is only one connection to the database. The query runs for much longer than I expect it to run for, and I think this is due to it using the incorrect subplan. As you can see, sub

Re: [GENERAL] Can checkpoint creation be parallel?

高健 wrote: >> The background writer and ordinary backends might write data >> (for their own rea>sons) that the >> checkpointer would have otherwise needed to write anyway. > > And does the ordinary backends refer to > the ones created when a client make a connection to PG? Yes. Yours, Laurenz Al

Re: [GENERAL] Hi, Friends, are there any ETL tools (free or commercial) available for PostgreSQL?

On Mon, Oct 7, 2013 at 8:32 PM, sunpeng wrote: > Hi, Friends, are there any ETL tools (free or commercial) available for > PostgreSQL? > > *ETL Tools for PostgreSQL::* Definition: An ETL process data to load into the database from a flat file A. Extract B. Transform C. Load 1. PGLoader - Load

Re: [GENERAL] Many, many materialised views - Performance?

On Oct 9, 2013, at 4:08, Kevin Grittner wrote: > Toby Corkindale wrote: > >> In this instance, we have a lot of queries that build certain aggregate >> results, which are very slow. The queries were initially all implemented >> as views, but then we started doing a type of materialising of our

Re: [GENERAL] Incorrect index being used

Jesse Long wrote: > I have the following query, run immediately after executing VACUUM in > the database. There is only one connection to the database. You should run ANALYZE, not VACUUM. > The query runs for much longer than I expect it to run for, and I think > this is due to it using the incor

Re: [GENERAL] Incorrect index being used

On 09/10/2013 12:10, Albe Laurenz wrote: Jesse Long wrote: I have the following query, run immediately after executing VACUUM in the database. There is only one connection to the database. You should run ANALYZE, not VACUUM. The query runs for much longer than I expect it to run for, and I th

Re: [GENERAL] Incorrect index being used

On 09/10/2013 12:57, Jesse Long wrote: On 09/10/2013 12:10, Albe Laurenz wrote: Jesse Long wrote: I have the following query, run immediately after executing VACUUM in the database. There is only one connection to the database. You should run ANALYZE, not VACUUM. The query runs for much long

Re: [GENERAL] Many, many materialised views - Performance?

On Tue, 8 Oct 2013 19:08:45 -0700 (PDT) Kevin Grittner wrote: > > In this instance, we have a lot of queries that build certain aggregate > > results, which are very slow. The queries were initially all implemented > > as views, but then we started doing a type of materialising of our own, > > t

Re: [GENERAL] pg_similarity

Now the Steps: $ USE_PGXS=1 make $ USE_PGXS=1 make install are working. But I still don't have the directory 'SHAREDIR/contrib/pg_similarity.sql'   Janek Sendrowski   -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql

Re: [GENERAL] Hi, Friends, are there any ETL tools (free or commercial) available for PostgreSQL?

On 09/10/2013, at 8:39 PM, raghu ram wrote: > > On Mon, Oct 7, 2013 at 8:32 PM, sunpeng wrote: > Hi, Friends, are there any ETL tools (free or commercial) available for > PostgreSQL? > > > ETL Tools for PostgreSQL:: > > Definition: An ETL process data to load into the database from a flat

Re: [GENERAL] Hi, Friends, are there any ETL tools (free or commercial) available for PostgreSQL?

On 09/10/2013, at 11:03 PM, Tony Theodore wrote: > On 09/10/2013, at 8:39 PM, raghu ram wrote: >> >> ETL Tools for PostgreSQL:: >> >> Definition: An ETL process data to load into the database from a flat file >> >> A. Extract >> B. Transform >> C. Load > > Not exactly an ETL tool, but I've

[GENERAL] no syntax error on limit1

Hi, Postgres 9.1.9 gives me no syntax error on this, but all the records: with a as (values (1),(2),(3)) select * from a limit1 Cheers, WBL -- "Quality comes from focus and clarity of purpose" -- Mark Shuttleworth

Re: [GENERAL] no syntax error on limit1

On 9 October 2013 14:24, Willy-Bas Loos wrote: > Hi, > > Postgres 9.1.9 gives me no syntax error on this, but all the records: > with a as (values > (1),(2),(3)) > select * > from a > limit1 > > Hi, that's quite OK. The "limit1" is just an alias for the table "a" Szymon

Re: [GENERAL] no syntax error on limit1

duh! thx. On Wed, Oct 9, 2013 at 2:30 PM, Szymon Guz wrote: > > On 9 October 2013 14:24, Willy-Bas Loos wrote: > >> Hi, >> >> Postgres 9.1.9 gives me no syntax error on this, but all the records: >> with a as (values >> (1),(2),(3)) >> select * >> from a >> limit1 >> >> > Hi, > that's quite OK

Re: [GENERAL] no syntax error on limit1

here the limit1 is a table alias， = as limit1 jov 在 2013-10-9 下午8:27，"Willy-Bas Loos" 写道： > Hi, > > Postgres 9.1.9 gives me no syntax error on this, but all the records: > with a as (values > (1),(2),(3)) > select * > from a > limit1 > > Cheers, > > WBL > > -- > "Quality comes from focus and clar

Re: [GENERAL] Incorrect index being used

Jesse Long wrote: > There is no problem with row visibility, there is only one connection to > the database - the connection I am using to do these selects. No idea why the plans cannot be used. It might be helpful to see the table and index definitions. > Thanks you for the advise regarding ANA

Re: [GENERAL] pg_similarity

Thanks a lot! Now it's working :)   Janek Sendrowksi -- 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 timeout error

On 10/08/2013 07:58 PM, 高健 wrote: Hello: My customer encountered some connection timeout, while using one primary-one standby streaming replication. The original log is japanese, because there are no error-code like oracle's ora-xxx, I tried to translate the japanese information into English, B

[GENERAL] I need more specific instructions for switching to digest mode for this list

Trying to switch to the digest didn't work. How do I find more specific details about switching? On Wed, Oct 9, 2013 at 12:27 AM, Bob Futrelle wrote: > set pgsql-general digest >

Re: [GENERAL] I need more specific instructions for switching to digest mode for this list

On 09/10/2013 16:47, Bob Futrelle wrote: > Trying to switch to the digest didn't work. > How do I find more specific details about switching? > > > On Wed, Oct 9, 2013 at 12:27 AM, Bob Futrelle > wrote: > > set pgsql-general digest > You sent your command

Re: [GENERAL] I need more specific instructions for switching to digest mode for this list

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Raymond O'Donnell > Sent: Wednesday, October 09, 2013 11:54 AM > To: Bob Futrelle > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] I need more specific inst

Re: [GENERAL] Incorrect index being used

Jesse Long writes: > The query runs for much longer than I expect it to run for, and I think > this is due to it using the incorrect subplan. As you can see, subplans > 1 and 3 make use of and index, but these subplans are not used. > Subplans and 4 are seqscan, and they are used. > How can I

Re: [GENERAL] String reverse funtion?

ginkgo36 wrote > Hello everyone > > I have to reverse a string like EA;BX;CA to CA;BX;EA. or EA,BX,CA to > CA,BX,EA > > Is there any function to do this? > > Thanks all! No. You will have to write your own. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.c

Re: [GENERAL] String reverse funtion?

On 10/9/2013 11:52 AM, David Johnston wrote: ginkgo36 wrote Hello everyone I have to reverse a string like EA;BX;CA to CA;BX;EA. or EA,BX,CA to CA,BX,EA Is there any function to do this? Thanks all! No. You will have to write your own. David J. Based upon the example, it's proba

Re: [GENERAL] String reverse funtion?

2013/10/9 John Meyer > On 10/9/2013 11:52 AM, David Johnston wrote: > >> ginkgo36 wrote >> >>> Hello everyone >>> I have to reverse a string like EA;BX;CA to CA;BX;EA. or EA,BX,CA to >>> CA,BX,EA >>> Is there any function to do this? >>> >>> Thanks all! >>> >> No. You will have to write your

Re: [GENERAL] String reverse funtion?

That works too. Point being ginkgo36 has his solution. On 10/9/2013 12:07 PM, Pavel Stehule wrote: 2013/10/9 John Meyer > On 10/9/2013 11:52 AM, David Johnston wrote: ginkgo36 wrote Hello everyone I have to revers

Re: [GENERAL] [HACKERS] Urgent Help Required

On 10/8/13 5:55 AM, shailesh singh wrote: > HINT: To avoid a database shutdown, execute a full-database VACUUM in "debug". > ERROR: could not access status of transaction 449971277 > DETAIL: could not open file "pg_clog/01AD": No such file or directory Unless I'm mistaken, that missing CLOG f

[GENERAL] declare constraint as valid

Hi, assuming a constraint is added to a table as NOT VALID. Now I know it IS valid. Can I simply declare it as valid by update pg_constraint set convalidated='t' where conrelid=(select c.oid from pg_class c join pg_namespace n on (n.oid=c.relnamespace)

Re: [GENERAL] streaming replication timeout error

Hello: Thanks for replying. The recovery.conf file on standby(DB2) is like that: standby_mode = 'on' primary_conninfo = 'host=DB1 port=5432 application_name=testpg user=postgres connect_timeout=10 keepalives_idle=5 keepalives_interval=1' recovery_target_timeline = 'latest' re

Re: [GENERAL] ERROR: invalid value "????" for "YYYY"

But from a user's perspective, why would it ever make sense that by adding an additional where clause, it actually brings in more data into the picture? If I have query returning 100 rows. Adding an additional where clause should only cut down the number of rows, not increase it. And the extra d

Re: [GENERAL] ERROR: invalid value "????" for "YYYY"

Brian Wong-2 wrote > But from a user's perspective, why would it ever make sense that by adding > an additional where clause, it actually brings in more data into the > picture? If I have query returning 100 rows. Adding an additional where > clause should only cut down the number of rows, not in

Re: [GENERAL] String reverse funtion?

Thanks alot everyone, Actually, I just start learning about Programming language. So, each your solution is a good suggestion for me to learning. Thanks so much. Please help me one User-defined Functions to solves this when my data like this: EA;BX;CA CA;EA BX;EA And when run UDFs, output is: CA;BX

Re: [HACKERS] [GENERAL] Urgent Help Required

On 10/8/2013 8:35 AM, Chris Travers wrote: First, while vacuum is usually preferred to vacuum full, in this case, I usually find that vacuum full clears up enough cruft to be worth it (not always, but especially if you are also having performance issues). IIRC, vacuum full was pretty broken i

[GENERAL] Forms for entering data into postgresql‏

I have developed an application using MS SQL. I have used MS Access for creating forms to enter data into the database. I am thinking of changing over to postgresql and would also like to use any other available open source tool for creating forms. Are there any free applications available for c

[GENERAL] Re: [GENERAL] Forms for entering data into postgresql‏

On Wed, Oct 9, 2013 at 7:05 PM, Sudhir P.B. wrote: > I have developed an application using MS SQL. I have used MS Access for > creating forms to enter data into the database. I am thinking of changing > over to postgresql and would also like to use any other available open > source tool for creat

Re: [GENERAL] streaming replication timeout error

On 10/09/2013 05:51 PM, 高健 wrote: Hello: Thanks for replying. The recovery.conf file on standby(DB2) is like that: standby_mode = 'on' primary_conninfo = 'host=DB1 port=5432 application_name=testpg user=postgres connect_timeout=10 keepalives_idle=5 keepalives_interval=1' re

[GENERAL] Re: [GENERAL] Forms for entering data into postgresql‏

On 10/09/2013 07:05 PM, Sudhir P.B. wrote: I have developed an application using MS SQL. I have used MS Access for creating forms to enter data into the database. I am thinking of changing over to postgresql and would also like to use any other available open source tool for creating forms. Are t

Re: [GENERAL] Many, many materialised views - Performance?

On 09/10/13 21:05, Alban Hertroys wrote: On Oct 9, 2013, at 4:08, Kevin Grittner wrote: Toby Corkindale wrote: In this instance, we have a lot of queries that build certain aggregate results, which are very slow. The queries were initially all implemented as views, but then we started doing

Re: [GENERAL] Many thousands of partitions

Hello, Thanks for sharing your experiences with the problem. W dniu 2013-10-09 00:47, "Gabriel E. Sánchez Martínez" pisze: Partioning seems to be a good idea if a single table would be too big to fit in your server's file cache, and also for management, since you can drop partitions of old dat

Re: [GENERAL] Tree structure

Sorry, got tangled up in this thing called 'real life'. If I understand you correctly, you want a prefix match, and sure there's a PostgreSQL extension for that: OK, that seems to do the job, thanks a lot. The only small quibble is that it's an extension. I'm quite surprised there seem to b