[BUGS] BUG #5943: jdbc client doesn't omit pg_toast_temp tables from schema views
The following bug has been logged online: Bug reference: 5943 Logged by: Daniel Ceregatti Email address: dan...@ceregatti.org PostgreSQL version: N/A Operating system: N/A Description:jdbc client doesn't omit pg_toast_temp tables from schema views Details: In the file org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java is the query: sql = "SELECT nspname AS TABLE_SCHEM FROM pg_catalog.pg_namespace WHERE nspname <> 'pg_toast' AND nspname !~ '^pg_temp_' ORDER BY TABLE_SCHEM"; Using Data Tools Platform in eclipse I was able to see all the pg_toast_temp_XXX schemas in the schema view. Using postgresql-9.0-801.jdbc4.jar Please note that RhodiumToad in #postgresql on irc.freenode.org actually discovered this based on a conversation he, ojacobson, and I had, and I'm simply reporting what he discovered. Thanks, Daniel -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5944: COPY FROM doesn't work with international characters
AcNumber character varying(20), AcName character varying(50) The database itself is: ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252' -Original Message- From: John R Pierce [mailto:pie...@hogranch.com] Sent: Wednesday, March 23, 2011 7:54 PM To: Nathan M. Davalos; PostgreSQL Bugs Subject: Re: [BUGS] BUG #5944: COPY FROM doesn't work with international characters On 03/23/11 5:42 PM, Nathan M. Davalos wrote: > Just in case the reply didn't go through the character in question is F1. > > I replied to the message using pgsql-bugs@postgresql.org, but I'm a wee bit > new to the bug reporting stuff using the mailing list. > > The hex of the file in total is > 32.33.30.30.30.32.09.41.6C.74.6F.20.44.65.73.65.6D.70.65.F1.6F.2C.20.53.2E.41.2E.20.44.65.20.43.2E.56.2E.0D.0A k, thats certainly win-1252. I see a tab after the 230002, then the rest of it is all one field, ending in a sequence. The table you're copying this data to, what fields does it have? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5944: COPY FROM doesn't work with international characters
On 03/23/11 5:42 PM, Nathan M. Davalos wrote: Just in case the reply didn't go through the character in question is F1. I replied to the message using pgsql-bugs@postgresql.org, but I'm a wee bit new to the bug reporting stuff using the mailing list. The hex of the file in total is 32.33.30.30.30.32.09.41.6C.74.6F.20.44.65.73.65.6D.70.65.F1.6F.2C.20.53.2E.41.2E.20.44.65.20.43.2E.56.2E.0D.0A k, thats certainly win-1252. I see a tab after the 230002, then the rest of it is all one field, ending in a sequence. The table you're copying this data to, what fields does it have? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5944: COPY FROM doesn't work with international characters
32333030303209416C746F20446573656D7065F16F2C20532E412E20446520432E562E0D0A The character in question is F1 -Original Message- From: John R Pierce [mailto:pie...@hogranch.com] Sent: Wednesday, March 23, 2011 6:49 PM To: Nathan M. Davalos Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #5944: COPY FROM doesn't work with international characters On 03/23/11 4:32 PM, Nathan Davalos wrote: > ... > SET CLIENT_ENCODING TO 'WIN1251'; > copy tmpintermediate from 'thefile.txt'; > > > Sample contents of thefile: > 230002Alto Desempeño, S.A. De C.V. > > When using WIN1251 or WIN1252 I get nothing in the second field, it just > ignores the data. Same thing for LATIN-1. > > When using UTF8 for client encoding I get this message: > ERROR: invalid byte sequence for encoding "UTF8": 0xf16f2c20 > CONTEXT: COPY tmpintermediate , line 1 what is the byte (binary) encoding of the file? in hex, ñ in win1251 == (no such character.win1251 is cyrillic) ñ in win1252 == F1 ñ in UTF-8== C3 B1 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Index Ignored Due To Use Of View
"Donald Fraser" writes: > I found the solution to the problem and it would therefore appear as though > this is not a bug! > If I change the function definition to be "STABLE" instead of "VOLATILE", > then the problem goes away. Yeah, that's intentional. If the planner flattens a view that has a function in its output list, things get a lot less predictable about when the function will be called. For a volatile function, that can matter a lot. We used to flatten such views anyway, but we got enough complaints that we changed it. http://archives.postgresql.org/pgsql-committers/2006-08/msg00358.php regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5944: COPY FROM doesn't work with international characters
On 03/23/11 4:32 PM, Nathan Davalos wrote: ... SET CLIENT_ENCODING TO 'WIN1251'; copy tmpintermediate from 'thefile.txt'; Sample contents of thefile: 230002 Alto Desempeño, S.A. De C.V. When using WIN1251 or WIN1252 I get nothing in the second field, it just ignores the data. Same thing for LATIN-1. When using UTF8 for client encoding I get this message: ERROR: invalid byte sequence for encoding "UTF8": 0xf16f2c20 CONTEXT: COPY tmpintermediate , line 1 what is the byte (binary) encoding of the file? in hex, ñ in win1251 == (no such character.win1251 is cyrillic) ñ in win1252 == F1 ñ in UTF-8== C3 B1 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5944: COPY FROM doesn't work with international characters
The following bug has been logged online: Bug reference: 5944 Logged by: Nathan Davalos Email address: n.dava...@sharedmarketing.com PostgreSQL version: 9 Operating system: Windows XP/2003 Description:COPY FROM doesn't work with international characters Details: I'm trying to load a tab delimited text file with COPY FROM under version 9. create table tmpintermediate ( AcNumber character varying(20), AcName character varying(50)); SET CLIENT_ENCODING TO 'WIN1251'; copy tmpintermediate from 'thefile.txt'; Sample contents of thefile: 230002 Alto Desempeño, S.A. De C.V. When using WIN1251 or WIN1252 I get nothing in the second field, it just ignores the data. Same thing for LATIN-1. When using UTF8 for client encoding I get this message: ERROR: invalid byte sequence for encoding "UTF8": 0xf16f2c20 CONTEXT: COPY tmpintermediate , line 1 Not sure what the problem is, I assumed encoding the database as UTF8 would just take any character. Inserting the data with an insert statement works perfectly normal. Is it something I'm doing wrong with COPY FROM? The database is UTF8, in 8.3 we were using WIN1252 for the database encoding, but when we migrated to 8.4 everything was forced to UTF8 regardless of what the locale was specified during install using pg_dumpall to migrate the data, so I just left things as UTF8. Not sure how to proceed with this one. We create all our databases using a customized template datatabase (we leave the ones that install with postgresql intact and untouched). The copy commands are being issued by pgadmin3 if that makes any difference. That and we use MicroOlap's PosgresDAC components for Delphi. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5942: pg_trgm.sql has cyclic dependency on type gtrgm creation
"Arthur Nascimento" writes:
> When creating the gtrgm type, it depends on gtrgm_{in,out}. These functions
> also depend on the type, so there is a cyclic dependency on creation of the
> type.
> The command psql -f sharedir/contrib/pg_trgm.sql crashes for me accusing
> this error.
Crashes how? That script works fine for everybody else. You might get
some notices but not a crash.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5942: pg_trgm.sql has cyclic dependency on type gtrgm creation
The following bug has been logged online:
Bug reference: 5942
Logged by: Arthur Nascimento
Email address: tur...@gmail.com
PostgreSQL version: 9.0.3
Operating system: Linux
Description:pg_trgm.sql has cyclic dependency on type gtrgm creation
Details:
When creating the gtrgm type, it depends on gtrgm_{in,out}. These functions
also depend on the type, so there is a cyclic dependency on creation of the
type.
The command psql -f sharedir/contrib/pg_trgm.sql crashes for me accusing
this error.
According to the CREATE TYPE documentation, this can be solved by adding a
"CREATE TYPE gtrgm;" line before the functions are defined. This solution
worked locally for me by including that command between lines 40 and 41 of
the file.
A snippet of the offending code of contrib/pg_trgm.sql, lines 40 to 55:
-- gist key
CREATE OR REPLACE FUNCTION gtrgm_in(cstring)
RETURNS gtrgm
AS '$libdir/pg_trgm'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION gtrgm_out(gtrgm)
RETURNS cstring
AS '$libdir/pg_trgm'
LANGUAGE C STRICT;
CREATE TYPE gtrgm (
INTERNALLENGTH = -1,
INPUT = gtrgm_in,
OUTPUT = gtrgm_out
);
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Warm Standby startup process unconditionally hangs
Excerpts from Simon Riggs's message of mié mar 23 17:37:11 -0300 2011: > The answer looks clear from here. What I'm stunned about is that we've > not had a report about this before now. (You replied there had been one). I've actually seen reports that the recovery process seems stuck but I've never had the chance to research it. About the patch: shouldn't the startup PID be reset eventually? -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Warm Standby startup process unconditionally hangs
On Mon, Mar 21, 2011 at 4:44 PM, Bernd Helmle wrote: > PostgreSQL is 8.4.7, WAL-Logshipping is actually using SkyTools 2.1.12 (both > installed from PGDG rpms), running with CentOS 5.5 XEN-VM with Kernel > 2.6.18-194.26.1.el5xen. > > recovery.conf on the standby installed by walmgr is > > restore_command = '/usr/bin/walmgr.py > /media/pgarchive/wal-config/wal-slave.ini xrestore %f "%p" %r' > #recovery_target_time= > #recovery_target_xid= > #recovery_target_inclusive=true > #recovery_target_timeline= > > The archive is located on a NFS share. We have seen the following happening > on a PostgreSQL Warm Standby: > > Once for a while the Startup Process just stops recovering from archive. No > more xlogs replayed, no more activity in the logs from the startup process. > We've seen this two or three times in the past, this time we had the chance > to do some further investigation what's happening. > > strace shows the startup process hanging with > > strace -p 31898 > Process 31898 attached - interrupt to quit > semop(425990, 0x7fff19919060, 1 > > Here is a backtrace from the startup process: > > (gdb) bt > #0 0x003e214d5337 in semop () from /lib64/libc.so.6 > #1 0x005a7ff3 in PGSemaphoreLock (sema=0x2b35a0140ce8, > interruptOK=1 '\001') at pg_sema.c:420 > #2 0x005c68a5 in LockBufferForCleanup (buffer=78428) at > bufmgr.c:2467 > #3 0x00485446 in RestoreBkpBlocks (lsn=, > record=0xcf2b830, cleanup=1 '\001') at xlog.c:3292 > #4 0x00460ce0 in heap2_redo (lsn=..., record=0x7fff19919060) at > heapam.c:4805 > #5 0x00482eff in StartupXLOG () at xlog.c:5665 > #6 0x00484cfb in StartupProcessMain () at xlog.c:8102 > #7 0x0049b5a8 in AuxiliaryProcessMain (argc=2, argv= optimized out>) at bootstrap.c:419 > #8 0x005b0ef9 in StartChildProcess (type=StartupProcess) at > postmaster.c:4319 > #9 0x005b30c7 in PostmasterMain (argc=5, argv=0xcf079e0) at > postmaster.c:1036 > #10 0x0056079e in main (argc=5, argv=) at > main.c:188 > > Looking at the code, it seems it is stuck in waiting for UnpinBuffer(), > ProcWaitForSignal(). Getting the backtrace for the bgwriter process, it > seems it is working as expected: > > (gdb) > #0 0x003e214cd1c3 in __select_nocancel () from /lib64/libc.so.6 > #1 0x006b086a in pg_usleep (microsec=) at > pgsleep.c:43 > #2 0x005ac00e in BackgroundWriterMain () at bgwriter.c:538 > #3 0x0049b5b7 in AuxiliaryProcessMain (argc=2, argv= optimized out>) at bootstrap.c:424 > #4 0x005b0ef9 in StartChildProcess (type=BgWriterProcess) at > postmaster.c:4319 > #5 0x005b4df6 in sigusr1_handler (postgres_signal_arg= optimized out>) at postmaster.c:4095 > #6 > #7 0x003e214cd1c3 in __select_nocancel () from /lib64/libc.so.6 > #8 0x005b1b5e in ServerLoop () at postmaster.c:1347 > #9 0x005b30dc in PostmasterMain (argc=5, argv=0xcf079e0) at > postmaster.c:1040 > #10 0x0056079e in main (argc=5, argv=) at > main.c:188 > > The logfile shows the following sequence before this issue: > > <@ 2011-03-21 13:56:46 CET 4d7a3ae1.7c9a-1457> LOG: restored log file > "000100200070" from archive > <@ 2011-03-21 13:56:46 CET 4d7a3ae2.7c9e-48794> DEBUG: updated min recovery > point to 20/704A6EC8 > <@ 2011-03-21 13:56:46 CET 4d7a3ae2.7c9e-48795> CONTEXT: writing block 157 > of relation base/70631854/70632521 > <@ 2011-03-21 13:56:46 CET 4d7a3ae2.7c9e-48796> DEBUG: updated min recovery > point to 20/7054BD78 > <@ 2011-03-21 13:56:46 CET 4d7a3ae2.7c9e-48797> CONTEXT: writing block 156 > of relation base/70631854/70632521 > <@ 2011-03-21 13:57:03 CET 4d7a3ae2.7c9e-48798> LOG: restartpoint complete: > wrote 4518 buffers (1.7%); write=225.415 s, sync=0.203 s, total=225.621 s > <@ 2011-03-21 13:57:03 CET 4d7a3ae2.7c9e-48799> LOG: recovery restart point > at 20/68244EC8 > <@ 2011-03-21 13:58:18 CET 4d7a3ae2.7c9e-48800> LOG: restartpoint starting: > time > <@ 2011-03-21 14:02:18 CET 4d7a3ae2.7c9e-48801> LOG: restartpoint complete: > wrote 3733 buffers (1.4%); write=239.997 s, sync=0.022 s, total=240.022 s > <@ 2011-03-21 14:02:18 CET 4d7a3ae2.7c9e-48802> LOG: recovery restart point > at 20/6BEB2EB0 > <@ 2011-03-21 14:03:18 CET 4d7a3ae2.7c9e-48803> DEBUG: skipping > restartpoint, already performed at 20/6BEB2EB0 > <@ 2011-03-21 14:03:33 CET 4d7a3ae2.7c9e-48804> DEBUG: skipping > restartpoint, already performed at 20/6BEB2EB0 > <@ 2011-03-21 14:03:48 CET 4d7a3ae2.7c9e-48805> DEBUG: skipping > restartpoint, already performed at 20/6BEB2EB0 > > After this, the startup process just "hangs", with the symptoms shown above. > I need to restart the standby now, because the customer wants to have it > back as soon as possible, but if you need more infos, please let me know. Bernd, I just realised that my email to you was private, not public. Copying whole message again here. The answer looks clear from here. What I'm stunned about is that we've not had a re
Re: [BUGS] TO_CHAR(timestamptz,datetimeformat) wrong after DST change
Hello Jonathan, the problem might be solved after the upcoming weekend. Because on the upcoming weekend most other countries of the world switch time. Some years ago USA was conform here - but then USA changed the time switching date for USA (or just for some states). Maybe that is the reason. Your system not got the update that USA is switching on another date then almost the rest of the world. Susanne On 21.03.2011 14:24, Jonathan Brinkman wrote: To make this even weirder, this effect only seems to happen to the 'postgres' user. When I use the 'bucardo' user, the time zone is correct! postgres@Cloud-DB1:~$ psql beta_cms_main -c "select now();" now --- 2011-03-21 08:22:37.521213-05 (1 row) postgres@Cloud-DB1:~$ psql beta_cms_main -c "SHOW TIME ZONE;" TimeZone -- EST (1 row) postgres@Cloud-DB1:~$ su - bucardo Password: bucardo@Cloud-DB1:~$ psql beta_cms_main -c "SHOW TIME ZONE;" TimeZone -- America/New_York (1 row) bucardo@Cloud-DB1:~$ psql beta_cms_main -c "select now();" now --- 2011-03-21 09:23:03.079692-04 (1 row) bucardo@Cloud-DB1:~$ logout postgres@Cloud-DB1:~$ psql beta_cms_main -c "SHOW TIME ZONE;" TimeZone -- EST (1 row) -Original Message- From: Jonathan Brinkman [mailto:j...@blackskytech.com] Sent: Monday, March 21, 2011 9:14 AM To: 'Tom Lane'; 'Kevin Grittner' Cc: 'pgsql-bugs@postgresql.org' Subject: RE: [BUGS] TO_CHAR(timestamptz,datetimeformat) wrong after DST change I understand now that I must use America/New_York for DST to function. I see in select * from pg_timezone_names ; that 'EDT' is a shortcut. I tried to SET TIME ZONE 'EDT'; but PG doesn't seem to like that. My problem is that the corrected time zone (America/New_York) doesn't seem to stick after updating. I update it in psql (cmd line) and within psql it returns correctly. But when I then view now() from command line the DST change is not there and time zone is again 'EST'. So: postgres@Cloud-DB1:~$ psql beta_cms_main -c "SHOW TIME ZONE;" TimeZone -- EST (1 row) postgres@Cloud-DB1:~$ psql beta_cms_main psql (8.4.7) Type "help" for help. beta_cms_main=# show time zone; TimeZone -- EST (1 row) beta_cms_main=# set time zone 'America/New_York'; SET beta_cms_main=# show time zone; TimeZone -- America/New_York (1 row) beta_cms_main=# \q postgres@Cloud-DB1:~$ psql beta_cms_main -c "SHOW TIME ZONE;" TimeZone -- EST (1 row) postgres@Cloud-DB1:~$ psql beta_cms_main -c "select now();" now --- 2011-03-21 08:09:07.029884-05 (1 row) [INCORRECT, SHOULD BE -04 and it is now 9:09AM, not 8:09AM] I enabled America/New_York in postgresql.conf and restarted PG but no change. I re-ran tzdata in Ubuntu but no change. I rebooted the server no change. -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Friday, March 18, 2011 12:47 PM To: Kevin Grittner Cc: j...@blackskytech.com; pgsql-bugs@postgresql.org Subject: Re: [BUGS] TO_CHAR(timestamptz,datetimeformat) wrong after DST change "Kevin Grittner" writes: "Jonathan Brinkman" wrote: I guess EST is not DST-friendly? EST stands for Eastern *Standard* Time, which is explicitly *not* under Daylight Saving Time. Right. SET TIMEZONE 'EST' gets you GMT-5 all year round. For background see this bit in src/timezone/data/northamerica: # From Arthur David Olson, 2005-12-19 # We generate the files specified below to guard against old files with # obsolete information being left in the time zone binary directory. # We limit the list to names that have appeared in previous versions of # this time zone package. # We do these as separate Zones rather than as Links to avoid problems if # a particular place changes whether it observes DST. # We put these specifications here in the northamerica file both to # increase the chances that they'll actually get compiled and to # avoid the need to duplicate the US rules in another file. # Zone NAMEGMTOFF RULES FORMAT [UNTIL] ZoneEST -5:00 - EST ZoneMST -7:00 - MST ZoneHST -10:00 - HST ZoneEST5EDT -5:00 US E%sT ZoneCST6CDT -6:00 US C%sT ZoneMST7MDT -7:00 US M%sT ZonePST8PDT -8:00 US P%sT (Note: the lack of a RULES entry means no DST rule.) regards, tom lane -- Susanne Ebrecht - 2ndQuadrant PostgreSQL Development, 24x7 Support, Training and Services www.2ndQuadrant.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Index Ignored Due To Use Of View
On Wed, Mar 23, 2011 at 8:10 AM, Donald Fraser wrote: > - Original Message - > > Sent: Wednesday, March 23, 2011 12:50 PM > Subject: Index Ignored Due To Use Of View > PostgreSQL 8.3.14 > OS: Linux Redhat 5.4 > > Note: I have used the same subject for this email taken from an email: > Posted 2011-02-24 13:29:22-08 by "David Johnston", because this seems to be > a very similar observation. > > Bug/Problem Summary: > We are using a simple query based on a simple view and the query optimizer > is not choosing an index. > The same query without the view is using an index. > The same query on an almost identical view, but having either removed a > single column which was generated via a function call or replace the > function call with equivalent SQL, then the query optimizer is choosing an > index. > > > I found the solution to the problem and it would therefore appear as though > this is not a bug! > If I change the function definition to be "STABLE" instead of "VOLATILE", > then the problem goes away. Also, it's bad practice to do order by/limit in the new definition like that. Leave them off, and do it in the calling query. I would write your function like this: CREATE OR REPLACE FUNCTION get_cmpyname(integer) RETURNS citext AS $$ SELECT CASE WHEN length(s_umbname) > 0 THEN s_umbname || '-' || s_res ELSE '' END FROM tbl_cmpy WHERE id = $1; $$ LANGUAGE sql STABLE STRICT; I took off the limit 1 because you probably don't need it. merlin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Index Ignored Due To Use Of View
- Original Message - Sent: Wednesday, March 23, 2011 12:50 PM Subject: Index Ignored Due To Use Of View PostgreSQL 8.3.14 OS: Linux Redhat 5.4 Note: I have used the same subject for this email taken from an email: Posted 2011-02-24 13:29:22-08 by "David Johnston", because this seems to be a very similar observation. Bug/Problem Summary: We are using a simple query based on a simple view and the query optimizer is not choosing an index. The same query without the view is using an index. The same query on an almost identical view, but having either removed a single column which was generated via a function call or replace the function call with equivalent SQL, then the query optimizer is choosing an index. I found the solution to the problem and it would therefore appear as though this is not a bug! If I change the function definition to be "STABLE" instead of "VOLATILE", then the problem goes away. Regards Donald Fraser
[BUGS] Index Ignored Due To Use Of View
PostgreSQL 8.3.14 OS: Linux Redhat 5.4 Note: I have used the same subject for this email taken from an email: Posted 2011-02-24 13:29:22-08 by "David Johnston", because this seems to be a very similar observation. Bug/Problem Summary: We are using a simple query based on a simple view and the query optimizer is not choosing an index. The same query without the view is using an index. The same query on an almost identical view, but having either removed a single column which was generated via a function call or replace the function call with equivalent SQL, then the query optimizer is choosing an index. Other notes: We observe the same behaviour on two separate databases running on separate servers (both the same version). The genetic query optimizer settings are all on defaults for these versions. Details; With the following view: CREATE OR REPLACE VIEW vu_tbl_news_web AS SELECT n.id, n.id_cmpy, n.id_news, n.id_newshdline, n.s_origcmpyname, n.s_hdline, n.s_news, n.b_amend, n.b_replace, n.dt_publish, n.tsv_hdline, n.tsv_news, n.b_hasorigdoc, (SELECT h.s_hdline FROM tbl_newshdline h WHERE h.id = n.id_newshdline LIMIT 1) AS s_hdlinetype, get_cmpyname(n.id_contrib) AS s_provider FROM tbl_news n WHERE n.dt_publish IS NOT NULL; Query: SELECT n.id, n.id_cmpy, n.s_origcmpyname, n.s_hdline, n.dt_publish FROM vu_tbl_news_web n ORDER BY n.dt_publish DESC LIMIT 25 Analyse produces: Limit (cost=180017.37..180017.43 rows=25 width=80) -> Sort (cost=180017.37..180110.54 rows=37267 width=80) Sort Key: n.dt_publish -> Subquery Scan n (cost=0.00..178965.72 rows=37267 width=80) -> Seq Scan on tbl_news n (cost=0.00..178593.05 rows=37267 width=1152) Filter: (dt_publish IS NOT NULL) SubPlan -> Limit (cost=0.00..4.29 rows=1 width=22) -> Seq Scan on tbl_newshdline h (cost=0.00..4.29 rows=1 width=22) Filter: (id = $0) Although the number of rows (37k) is small, there is a lot of data in some of the columns so, with a seq. scan its taking nearly 2 seconds. Compared to 16ms when using an index. Now if I perform the same query without using the view, such as: SELECT n.id, n.id_cmpy, n.s_origcmpyname, n.s_hdline, n.dt_publish FROM tbl_news n WHERE n.dt_publish IS NOT NULL ORDER BY n.dt_publish DESC LIMIT 25 Analyse produces: Limit (cost=0.00..21.34 rows=25 width=73) -> Index Scan Backward using tbl_news_publish1_key on tbl_news n (cost=0.00..31807.05 rows=37267 width=73) Finally I discovered that if I remove a column from the view, that is generated via a function, then all works as expected? CREATE OR REPLACE VIEW vu_tbl_news_web3 AS SELECT n.id, n.id_cmpy, n.id_news, n.id_newshdline, n.s_origcmpyname, n.s_hdline, n.s_news, n.b_amend, n.b_replace, n.dt_publish, n.tsv_hdline, n.tsv_news, n.b_hasorigdoc, (SELECT h.s_hdline FROM tbl_newshdline h WHERE h.id = n.id_newshdline LIMIT 1) AS s_hdlinetype FROM tbl_news n WHERE n.dt_publish IS NOT NULL; SELECT n.id, n.id_cmpy, n.s_origcmpyname, n.s_hdline, n.dt_publish FROM vu_tbl_news_web3 n ORDER BY n.dt_publish DESC LIMIT 25 Analyse produces: Limit (cost=0.00..21.34 rows=25 width=73) -> Index Scan Backward using tbl_news_publish1_key on tbl_news n (cost=0.00..31807.05 rows=37267 width=73) The definition of the function used is: CREATE OR REPLACE FUNCTION get_cmpyname(integer) RETURNS citext AS $BODY$ DECLARE idcmpy ALIAS FOR $1; sumb citext; sres citext; BEGIN SELECT INTO sumb, sres s_umbname, s_name FROM tbl_cmpy WHERE (id = idcmpy) LIMIT 1; IF length(sumb) > 0 THEN sres := sumb || ' - ' || sres; END IF; RETURN sres; END $BODY$ LANGUAGE 'plpgsql' VOLATILE STRICT SECURITY DEFINER COST 100; I also noted that if I replace the function call in the view with equivalent SQL: (SELECT (CASE WHEN length(c.s_umbname) > 0 THEN c.s_umbname || ' - '::citext || c.s_name ELSE c.s_name END) AS s_cmpyname FROM tbl_cmpy c WHERE c.id = n.id_contrib LIMIT 1) AS s_provider the problem also goes away. Regards Donald Fraser
Re: [BUGS] postgres 9 streaming replication
Khadtare, Sharad wrote: Hi, Problem solved after removing trigger entry from recovery.conf file Thx for help Regards, Sharad K -Original Message- From: Fujii Masao [mailto:masao.fu...@gmail.com] Sent: Tuesday, January 25, 2011 5:55 PM To: Khadtare, Sharad Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] postgres 9 streaming replication On Tue, Jan 25, 2011 at 8:59 PM, Khadtare, Sharad wrote: Pls find below logfile of standby and recovery.conf in standby data directory. bash-3.2$ cat logfile LOG: database system was interrupted while in recovery at log time 2011-01-25 05:28:35 EST HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target. LOG: entering standby mode LOG: restored log file "00010037" from archive LOG: redo starts at 0/3720 LOG: consistent recovery state reached at 0/3800 LOG: database system is ready to accept read only connections cp: cannot stat `/db/postgres/archive/00010038': No such file or directory LOG: unexpected pageaddr 0/3000 in log file 0, segment 56, offset 0 cp: cannot stat `/db/postgres/archive/00010038': No such file or directory LOG: trigger file found: /db/postgres/trigger The above message means that you created the trigger file and promoted the standby server to the master. Since the standby was running as the master, walreceiver was not invoked in the standby. To start replication, you need to create the standby (taking the base backup from the master is required) and start it after you *ensure* that there is no trigger file in the standby. I hope this helps.. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center I have a same problem trying to get slave replication using 9.0 streaming replication. After the solution, I still not able to get through. First, I scp the whole data directory from master to slave server. In master server, I added the following lines in pg_hba.conf. host replication my_user_name slave_ip/32 trust host all all x.x.x.x/32 trust Note: my_user_name replaced with my real super user name and slave_ip replaced with slave server's ip. In master server, I also added the following lines in postgresql.conf. wal_level = hot_standby max_wal_senders = 5 wal_keep_segments = 32 listen_addresses='*' In slave server, I added the following line in postgresql.conf. hot_standby = on In slave server, I added the following line in recovery.conf. standby_mode = 'on' primary_conninfo = 'host=master_ip port=5432 user=my_user_name' trigger_file = '/path_to/pgsql.trigger' Note: my_user_name replaced with my real super user name and master_ip replaced with master server's ip. I removed trigger_file from recovery.conf and restart the slave. It still have the same problem. I then started master and slave servers. I got the error message from slave pg_log. ... cp: cannot stat `/path_to/arch_replicate/0001004A001F': No such file or directory cp: cannot stat `/path_to/arch_replicate/0001004A001F': No such file or directory 107602011-03-22 09:42:03 EDTFATAL: could not connect to the primary server: FATAL: no pg_hba.conf entry for replication connection from host "slave_server_ip", user "my_super_user_name" I got the error message from master pg_log. ... 1380902011-03-22 09:37:51 EDTLOG: connection received: host=slave_server_ip port=51686 1380902011-03-22 09:37:51 EDTauthenticationFATAL: no pg_hba.conf entry for replication connection from host "slave_server_ip", user "my_super_user_name" 1381002011-03-22 09:37:56 EDTLOG: connection received: host=slave_server_ip port=51687 1381002011-03-22 09:37:56 EDTauthenticationFATAL: no pg_hba.conf entry for replication connection from host "slave_server_ip", user "my_super_user_name" 1381102011-03-22 09:38:00 EDTLOG: connection received: host=slave_server_ip port=51688 1381102011-03-22 09:38:00 EDTauthenticationFATAL: no pg_hba.conf entry for replication connection from host "slave_server_ip", user "my_super_user_name" I was able to psql connect from slave to master or from master to slave by using. From slave server, I can connect from slave to master by the following command. psql database_name -f master_ip -p 5432 From master server, I can connect from master to slave by the following command. psql database_name -f slave_ip -p 5432 I have no idea why I keep getting the message:\ could not connect to the primary server: FATAL: no pg_hba.conf entry for replication connection from host "slave_server_ip", user "my_super_user_name" Your help will be highly appreciated. Alex Lai -- Best regards, Alex Lai OMI SIPS DBA ADNET Systems , Inc. 7515 Mission Drive, Suite A100 Lanham, MD 20706 301-352-4657 (phone) 301-352-0437 (fax) a...@sesda2.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make c
