[BUGS] BUG #2223: Misleading info in docs on volatility level of functions
The following bug has been logged online: Bug reference: 2223 Logged by: Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1 Operating system: linux Description:Misleading info in docs on volatility level of functions Details: Hi, all! The documentation on how to and why declare a function STABLE or IMMUTABLE is very laconic. It makes clear what promises I make when determining the volatility of my functions but it fails to point out what exactly I can expect in turn for my promises. See also the discussion on bug#2150 on the pgsql-bugs list. Thanks for bothering. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[BUGS] BUG #2224: unlogical syntax error
The following bug has been logged online: Bug reference: 2224 Logged by: Hervé Hénoch Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.2 Operating system: Linux FC4 Description:unlogical syntax error Details: I do this request : select id_caisse as caisse from adm_pat where nip = '20020523' ; Under PgAdmin this request work fine but via JDBC I have the following messages server side : CETLOG: statement: PREPARE AS SELECT id_caisse AS caisse FROM adm_pat WHERE nip = '20020523' CETLOG: statement: EXECUTE [PREPARE: SELECT id_caisse AS caisse FROM adm_pat WHERE nip = '20020523'] CETLOG: statement: PREPARE AS SELECT adm_pat."nip", id_caisse caisse FROM adm_pat WHERE nip = '20020523' CETERROR: syntax error at or near "caisse" at character 33 CETLOG: statement: PREPARE AS SELECT id_caisse AS caisse FROM adm_pat WHERE nip = '20020523' CETLOG: statement: EXECUTE [PREPARE: SELECT id_caisse AS caisse FROM adm_pat WHERE nip = '20020523'] The table adm_pat does not have another column named caisse. So what is the problem (if problem). Thanks a lot. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [BUGS] BUG #2221: Bad delimiters allowed in COPY ...
David Fetter wrote:
+ /* Disallow BADCHARS characters */
+ if (strcspn(cstate->delim, BADCHARS) != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+errmsg("COPY delimiter cannot be \"%#02x\"",
+ *cstate->delim)));
+
Is ERRCODE_FEATURE_NOT_SUPPORTED the right errcode? This isn't a
missing feature; we are performing a sanity check here. We can
reasonably expect never to support CR, LF or \ as the text delimiter.
Maybe ERRCODE_INVALID_PARAMETER_VALUE ? Or maybe we need a new one.
Also, I would probably make the format %#.02x so the result would look
like 0x0d (for a CR).
(I bet David never thought there would so much fuss over a handful of
lines of code)
cheers
andrew
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2224: unlogical syntax error
"Hervé Hénoch" <[EMAIL PROTECTED]> writes: > CETLOG: statement: PREPARE AS SELECT adm_pat."nip", id_caisse > caisse FROM adm_pat WHERE nip = '20020523' > CETERROR: syntax error at or near "caisse" at character 33 "AS" before a column alias is not optional in Postgres. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] Endless loop in ExecNestLoop
Hi,
The symptom:
The PostgreSQL backend sometimes does not terminate a query. (Killed after
the backend consumed 1h30 of CPU time.)
That query usually executes in less than 2 seconds, see also the explain
analyze.
Version: 8.0.2
Query:
select cel.objid,
cel.shortname, ser.shortname,
con.isInventoryFlag,
dev.objid, dty.objid, loc.objid,
dev.shortname, loc.shortname, loc.name, man.shortName,
dty.type, dev.serNr, dev.invNr, dev.component,
loc.zip, loc.city, con.shortname, con.name,
CASE
WHEN CURRENT_DATE between loc.locationstateFrom and
loc.locationstateTo THEN loc.locationstateText
WHEN CURRENT_DATE between loc.locationstate2From and
loc.locationstate2To THEN loc.locationstate2Text
ELSE null
END
, CASE
WHEN CURRENT_DATE between loc.locationstateFrom and
loc.locationstateTo THEN loc.locationstateColorCode
WHEN CURRENT_DATE between loc.locationstate2From and
loc.locationstate2To THEN loc.locationstate2ColorCode
ELSE null
END
,
loc.locationcategory, loc.province
from contracts con, contractelements cel
left outer join servicetypes ser on ser.objid = cel.ser_objid,
devices dev, locations loc, devicetypes dty, bpartners man
where con.csp_objid in
(587703,587726,728917,969346,1043154,1368907,1368915,1368944,4176279,4492405,4493459,5682484,7465538)
and cel.con_objid = con.objid
and dev.cel_objid = cel.objid
and loc.objid = dev.loc_objid
and dty.objid = dev.dty_objid
and man.objid = dty.bpa_objid
and upper(cel.isActiv) = 'Y'
and upper(coalesce(dev.isActiv,'Y')) = 'Y'
and upper(coalesce(dev.IsCommittedSP,'Y')) = 'Y'
and upper(coalesce(dev.IsCommittedCust,'Y')) = 'Y'
and upper(loc.shortName) = 'WNB531'
order by 18 ASC
Explain Analyze:
Sort (cost=12084.89..12084.89 rows=1 width=477) (actual
time=1344.417..1344.424 rows=5 loops=1)
Sort Key: con.shortname
-> Nested Loop (cost=79.18..12084.88 rows=1 width=477) (actual
time=977.884..1343.987 rows=5 loops=1)
-> Nested Loop (cost=79.18..12081.71 rows=1 width=130) (actual
time=6.363..1215.421 rows=12829 loops=1)
-> Nested Loop (cost=79.18..12078.64 rows=1 width=125)
(actual time=6.315..1045.691 rows=12829 loops=1)
-> Nested Loop (cost=79.18..12075.58 rows=1 width=111)
(actual time=6.256..855.119 rows=12829 loops=1)
-> Hash Join (cost=79.18..4321.10 rows=8
width=52) (actual time=5.668..666.173 rows=101 loops=1)
Hash Cond: ("outer".con_objid =
"inner".objid)
-> Hash Left Join (cost=1.18..4241.09
rows=385 width=27) (actual time=3.067..535.397 rows=73383 loops=1)
Hash Cond: ("outer".ser_objid =
"inner".objid)
-> Seq Scan on contractelements cel
(cost=0.00..4237.98 rows=385 width=21) (actual time=2.869..285.115 rows=73383
loops=1)
Filter: (upper((isactiv)::text) =
'Y'::text)
-> Hash (cost=1.14..1.14 rows=14
width=14) (actual time=0.092..0.092 rows=14 loops=1)
-> Seq Scan on servicetypes ser
(cost=0.00..1.14 rows=14 width=14) (actual time=0.024..0.052 rows=14 loops=1)
-> Hash (cost=77.82..77.82 rows=73
width=33) (actual time=0.378..0.378 rows=31 loops=1)
-> Bitmap Heap Scan on contracts con
(cost=26.26..77.82 rows=73 width=33) (actual time=0.196..0.304 rows=31
loops=1)
Recheck Cond: ((csp_objid =
587703) OR (csp_objid = 587726) OR (csp_objid = 728917) OR (csp_objid =
969346) OR (csp_objid = 1043154) OR (csp_objid = 1368907) OR (csp_objid =
1368915) OR (csp_objid = 1368944) OR (csp_objid = 4176279) OR (csp_objid =
4492405) OR (csp_objid = 4493459) OR (csp_objid = 5682484) OR (csp_objid =
7465538))
-> BitmapOr (cost=26.26..26.26
rows=74 width=0) (actual time=0.160..0.160 rows=0 loops=1)
-> Bitmap Index Scan on
contracts_csp_objid (cost=0.00..2.12 rows=34 width=0) (actual
time=0.077..0.077 rows=31 loops=1)
Index Cond:
(csp_objid = 587703)
-> Bitmap Index Scan on
contracts_csp_objid (cost=0.00..2.01 rows=3 width=0) (actual
time=0.005..0.005 rows=0 loops=1)
Index Cond:
(csp_objid = 587726)
-> Bitmap Index Scan on
contracts_csp_objid (cost=0.00..2.01 rows=3 width=0) (actua
[BUGS] Shared library issue in Windows
Hi, This is a reposting of my email from win32 hackers list. Original Issue: http://archives.postgresql.org/pgsql-hackers-win32/2006-01/msg0.php Below I have given the exact scenario which I can replicate in three different systems. Anybody who has faced this kind of issue please let me know if you have a way to get the psql to be able to use the dynamic_library_path right away. Also I think the documentation at http://www.postgresql.org/docs/8.1/static/runtime-config-client.html should show \\ instead of \ for Windows environment e.g dynamic_library_path = 'C:\\tools\\postgresql;H:\\my_project\\lib;$libdir' Thanks & Regards, Mehul -Original Message- From: Mehul Doshi-A20614 Sent: Friday, January 27, 2006 11:45 AM To: Magnus Hagander; [EMAIL PROTECTED] Cc: Mehul Doshi-A20614 Subject: RE: [pgsql-hackers-win32] Query on support for trigger functions defined in a Shared library in Windows Hi, Here's a weird scenario that seems to be coming out of so many different options. a) When the dll is placed in C:\Program Files\PostgreSQL\8.0\bin it works. b) When the dll is placed in C:\Program Files\Test\lib, it fails if dynamic_library_path is set to 'C:\Program Files\Test\lib;$libdir' However I had help from two people on this, i) Jean Marc: Replace \ with / i.e dynamic_library_path is set to 'C:/Program Files/Test/lib;$libdir' ii) Thomas Hallgreen: Do \\ instead of just \. i.e. dynamic_library_path is set to 'C:\\Program Files\\Test\\lib;$libdir' Now both of these "start to" work after some time only. There is no logic that I can explain as to how they work. Here's what I have tried to do to understand this behavior. a) Uninstall postgres completely and delete the data folder. b) Re-install Postgres. c) Modify the postgresql.conf file with either of the methods. I used method (ii) since it is more Windows like. d) Modify the System Variable PATH to contain the extra term "C:\Program Files\Test\lib" d) Now I stop the service, start the service. I even rebooted my system. e) On a new command prompt, I test it out ... Error as before. I can verify the PATH variable does have C:\Program Files\Test\lib in it. f) Anyways since I can't do much, I go back and add the dll to the bin folder and start using it. g) I can now go back to first command prompt and retest it starts to work!!!. It works with libdir. i)I can also go back and modify and delete the dll file that I placed in the bin folder. j) I can again retest the dll and it works fine. Infact it starts to take the dynamic path perfectly. test=# CREATE OR REPLACE FUNCTION trigf_test_pid_insert() RETURNS trigger test-# AS 'testrigfuncs' test-# LANGUAGE C ; CREATE FUNCTION test=# \q I have tried this on 4 different systems running Windows XP (Service Packs 1 & 2) using PostgreSQL 8.0 as well as PostgreSQL 8.1. Any clues as to why it won't work till I placed the dll in Postgresql\bin folder and then once I have placed the dll there and used it, it starts to work at either places even if I delete the dll from the bin folder. Thanks & Regards, Mehul ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Endless loop in ExecNestLoop
Philipp Reisner <[EMAIL PROTECTED]> writes: > Version: 8.0.2 I don't think so ... neither bitmap scans nor slot_getattr exist in 8.0. > Is this bug-report of any use so far ? Not a lot. You need to find out what the difference is between the cases where the query runs quickly and those where it doesn't. I'm betting that the planner is choosing a very bad plan in the slow cases, but there's not a lot of evidence here to show what or why. The explain analyze output shows two levels of hash join underneath four levels of nestloop join, whereas the stack trace looks like there are five levels of nestloop and only one hash. So this is some evidence that a different plan is being used in the slow cases. The stack trace doesn't provide nearly enough info about what that plan is, though. Do you have geqo_threshold set to less than its default value? Do you sometimes execute the query with different sets of parameters? Either of these might lead to changes of plan. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2224: unlogical syntax error
On Mon, Jan 30, 2006 at 10:47:10AM +, Herv Hnoch wrote: > CETLOG: statement: PREPARE AS SELECT adm_pat."nip", id_caisse > caisse FROM adm_pat WHERE nip = '20020523' > > CETERROR: syntax error at or near "caisse" at character 33 Are you writing this query or is JDBC generating it? The AS keyword is required for column aliases; its absence is causing the syntax error. test=> CREATE TABLE foo (abc text); test=> SELECT abc AS xyz FROM foo; xyz - (0 rows) test=> SELECT abc xyz FROM foo; ERROR: syntax error at or near "xyz" at character 12 LINE 1: SELECT abc xyz FROM foo; ^ -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [BUGS] BUG #2221: Bad delimiters allowed in COPY ...
On Mon, Jan 30, 2006 at 08:21:34AM -0500, Andrew Dunstan wrote:
>
>
> David Fetter wrote:
>
> >
> >+/* Disallow BADCHARS characters */
> >+if (strcspn(cstate->delim, BADCHARS) != 1)
> >+ereport(ERROR,
> >+(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> >+ errmsg("COPY delimiter cannot be \"%#02x\"",
> >+*cstate->delim)));
> >+
>
> Is ERRCODE_FEATURE_NOT_SUPPORTED the right errcode? This isn't a
> missing feature; we are performing a sanity check here. We can
> reasonably expect never to support CR, LF or \ as the text
> delimiter.
I guess that depends on whether we ever plan to allow people to set
the output record separator to something other than CR?LF.
> Maybe ERRCODE_INVALID_PARAMETER_VALUE ? Or maybe we need a new one.
>
> Also, I would probably make the format %#.02x so the result would
> look like 0x0d (for a CR).
>
> (I bet David never thought there would so much fuss over a handful
> of lines of code)
Actually, I'm happy to see it's getting QA. COPY is something that
has Consequences⢠if anything goes wrong with it, so I'd rather do
best efforts up front to get it right. :)
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778
Remember to vote!
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [BUGS] Bug#347548: DOMAIN CHECK constraint bypassed
On Sat, 2006-01-28 at 20:17 +0100, Peter Eisentraut wrote: > This bug was reported to Debian. Comments? AFAICS I fixed this a few weeks ago (post-8.1.2): http://archives.postgresql.org/pgsql-committers/2006-01/msg00209.php http://archives.postgresql.org/pgsql-patches/2006-01/msg00139.php I get the following results with the test script on REL8_1_STABLE: Insert string was allowed, OK DBD::Pg::db do failed: ERROR: value for domain absdirpath violates check constraint "absdirpath_check" Insert empty string was disallowed, OK DBD::Pg::st execute failed: ERROR: value for domain absdirpath violates check constraint "absdirpath_check" Insert empty string via bind was disallowed, OK DBD::Pg::st execute failed: ERROR: null value in column "basedir" violates not-null constraint Insert NULL via bind was disallowed, OK (FWIW I certainly agree with Tom that the domain implementation needs a fair bit of work.) -Neil ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2223: Misleading info in docs on volatility level of functions
This was recently discussed on -docs and I believe a patch was applied to at least HEAD. On Mon, Jan 30, 2006 at 10:33:35AM +, [EMAIL PROTECTED] wrote: > > The following bug has been logged online: > > Bug reference: 2223 > Logged by: > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.1 > Operating system: linux > Description:Misleading info in docs on volatility level of functions > Details: > > Hi, all! > The documentation on how to and why declare a function STABLE or IMMUTABLE > is very laconic. It makes clear what promises I make when determining the > volatility of my functions but it fails to point out what exactly I can > expect in turn for my promises. > See also the discussion on bug#2150 on the pgsql-bugs list. > Thanks for bothering. > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] Bug#347548: DOMAIN CHECK constraint bypassed
Neil Conway <[EMAIL PROTECTED]> writes: > On Sat, 2006-01-28 at 20:17 +0100, Peter Eisentraut wrote: >> This bug was reported to Debian. Comments? > AFAICS I fixed this a few weeks ago (post-8.1.2): You only fixed the bind-parameter case, though, no? The problem is still rampant in the PLs. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] Bug#347548: DOMAIN CHECK constraint bypassed
On Mon, 2006-01-30 at 20:45 -0500, Tom Lane wrote: > You only fixed the bind-parameter case, though, no? The problem is > still rampant in the PLs. Right: the bug report was specific to the bind parameter case. Domain constraints should also be checked before returning values of a domain type from all of the procedural languages, and before casting a value to a domain type in PL/PgSQL. This can be done by adding constraint checks to each PL individually, like this patch for PL/PgSQL's return value: http://archives.postgresql.org/pgsql-patches/2006-01/msg00107.php (The patch hasn't been applied because there is some additional infrastructure needed to get good performance.) We could do similar work for each PL. That actually wouldn't be too bad: adding the necessary domain constraint information (plus sinval support) to the typcache would allow most of the code to be shared. Or we could refactor the code more cleanly, as Tom suggests -- it's not clear to me quite how to do that without accepting a performance hit, though... -Neil ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
