[BUGS] BUG #4412: Check constraints cannot be added to the table for fields that are mixed case
The following bug has been logged online: Bug reference: 4412 Logged by: Kevin Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.15 Operating system: Gentoo Linux Description:Check constraints cannot be added to the table for fields that are mixed case Details: Check constraints cannot be added to the table for fields that are mixed case. Example - field employeeName in table Employees - ALTER TABLE "Employees" ADD CONSTRAINT "employeeNameTest" CHECK (employeeName != 'Kevin') results in --- ERROR: column "employeename" does not exist - I'm new to postgreSql, and using phppgadmin (also tried this with psql), so haven't tried putting check directly on the field/column instead of the table. Also, the constraint I'm really trying to write requires referencing 2 columns. For now, I switched the field names (to employee_name for example). -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] I have a question about using index in order statement.
Question: I have a question about using index in order statement. Why index ix_2 work by Seq Scan and index ix_3 work by Index Scan. Example : ix_2 condition : When I try explain select * from a_test order by code_ desc Postgresql response Sort (cost=11815.08..11852.56 rows=14990 width=56) Sort Key: code_ -> Seq Scan on a_test (cost=1.00..10260.90 rows=14990 width=56) ix_3 condition : When I try explain select * from a_test order by lower(code_) desc Postgresql response Index Scan using ix_3 on a_test (cost=0.00..769.27 rows=14990 width=18) Table schema : CREATE TABLE a_test ( t_key_ bigint NOT NULL, code_ character varying(15) ) WITH (OIDS=TRUE); ALTER TABLE a_test OWNER TO postgres; CREATE INDEX ix_2 ON a_test USING btree (code_ DESC); CREATE INDEX ix_3 ON a_test USING btree (lower(code_::text) DESC);
[BUGS] BUG #4245: Product Name...
The following bug has been logged online: Bug reference: 4245 Logged by: Kevin Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Operating system: Windows Description:Product Name... Details: Last week I placed a new product in the catalog. By mistake I named the product "Kevin Banks", not "LabKey Server". Is this something you can fix for me? Kevin (425) 443-0428 -- 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 #7659: LDAP auth does not search the subtree
The following bug has been logged on the website: Bug reference: 7659 Logged by: Kevin Smith Email address: ke...@rootsmith.ca PostgreSQL version: 9.2.1 Operating system: CentOS5 Description: I have the following in my pg_hba.conf file: host all +ldap 127.0.0.1/32 ldap ldapserver= ldapport=389 ldapbasedb="" ldapbinddn="" ldapbindpasswd= ldapsearchattribute= If I try to connect from the localhost with a valid ldap account, it fails. Note that the is located in objects, one level deeper than the given. The error in the log is as follows: could not search LDAP for filter "(=)" on server "": error code 1 However, when I do the following on the command line, it works: ldapsearch -x -L -b "" -D "" -w -H ldap://:389 "(=my_user)" When I change the configuration in pg_hba.conf so that the ldapbasedn is exactly on the same level as where the user resides, it works perfectly. The documentation states "The search will be performed over the subtree at ldapbasedn" but this does not appear to be the case from my testing. The scope appears to be defaulting to be just searching the base. -- 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 #4287: Will not boot
>>> "Bob Thompson" <[EMAIL PROTECTED]> wrote: > What we have is an apparent security problem on your computer. Perhaps > problem isn't the correct word, but security is so tight on your computer > that the program cannot do a network loopback to itself on IP address > 127.0.0.1. Exactly what error message do you get in response to what action? (Copy and paste if possible.) -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] memory leak in 8.2.4
ete until he killed the five processes still left trying to run the functions. The database started OK: [2008-07-17 07:22:42.010 CDT] 17569 LOG: database system was shut down at 2008-07-17 07:21:57 CDT [2008-07-17 07:22:42.010 CDT] 17569 LOG: checkpoint record is at 568/B428B38 [2008-07-17 07:22:42.010 CDT] 17569 LOG: redo record is at 568/B428B38; undo record is at 0/0; shutdown TRUE [2008-07-17 07:22:42.010 CDT] 17569 LOG: next transaction ID: 1/512039718; next OID: 5164114 [2008-07-17 07:22:42.010 CDT] 17569 LOG: next MultiXactId: 1; next MultiXactOffset: 0 [2008-07-17 07:22:42.010 CDT] 17569 LOG: database system is ready I've requested that they check pg_locks and pg_stat_activity if this happens again. I suspect that the most interesting information was lost when the kernel killed the postgres backend; but, should we catch one of these in progress, and have an open connection which functions for us, what other diagnostic steps would be good? [EMAIL PROTECTED]:/opt/ccap/bigbird/jars> uname -a Linux ATHENA 2.6.5-7.244-bigsmp #1 SMP Mon Dec 12 18:32:25 UTC 2005 i686 i686 i386 GNU/Linux [EMAIL PROTECTED]:/opt/ccap/bigbird/jars> cat /proc/version Linux version 2.6.5-7.244-bigsmp ([EMAIL PROTECTED]) (gcc version 3.3.3 (SuSE Linux)) #1 SMP Mon Dec 12 18:32:25 UTC 2005 [EMAIL PROTECTED]:/opt/ccap/bigbird/jars> cat /etc/SuSE-release SUSE LINUX Enterprise Server 9 (i586) VERSION = 9 PATCHLEVEL = 3 ATHENA:~ # free -m total used free sharedbuffers cached Mem: 12177 11789388 0 0 11038 -/+ buffers/cache:751 11426 Swap: 1027 5 1021 ATHENA:~ # df -h FilesystemSize Used Avail Use% Mounted on /dev/sda2 68G 5.6G 62G 9% / tmpfs 6.0G 16K 6.0G 1% /dev/shm /dev/sdb1 821G 269G 553G 33% /var/pgsql/data 8 of these: Intel(R) Xeon(TM) MP CPU 3.00GHz BBU RAID controller with 256 MB of RAM [EMAIL PROTECTED]:~> pg_config BINDIR = /usr/local/pgsql-8.2.4/bin DOCDIR = /usr/local/pgsql-8.2.4/doc INCLUDEDIR = /usr/local/pgsql-8.2.4/include PKGINCLUDEDIR = /usr/local/pgsql-8.2.4/include INCLUDEDIR-SERVER = /usr/local/pgsql-8.2.4/include/server LIBDIR = /usr/local/pgsql-8.2.4/lib PKGLIBDIR = /usr/local/pgsql-8.2.4/lib LOCALEDIR = MANDIR = /usr/local/pgsql-8.2.4/man SHAREDIR = /usr/local/pgsql-8.2.4/share SYSCONFDIR = /usr/local/pgsql-8.2.4/etc PGXS = /usr/local/pgsql-8.2.4/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--prefix=/usr/local/pgsql-8.2.4' '--enable-integer-datetimes' '--enable-debug' '--disable-nls' CC = gcc CPPFLAGS = -D_GNU_SOURCE CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing -g CFLAGS_SL = -fpic LDFLAGS = -Wl,-rpath,'/usr/local/pgsql-8.2.4/lib' LDFLAGS_SL = LIBS = -lpgport -lz -lreadline -lcrypt -ldl -lm VERSION = PostgreSQL 8.2.4 listen_addresses = '*' max_connections = 200 shared_buffers = 160MB temp_buffers = 50MB work_mem = 10MB maintenance_work_mem = 160MB max_fsm_pages = 80 bgwriter_lru_percent = 20.0 bgwriter_lru_maxpages = 200 bgwriter_all_percent = 10.0 bgwriter_all_maxpages = 600 wal_buffers = 160kB checkpoint_segments = 10 archive_command = '' random_page_cost = 2.0 effective_cache_size = 10GB redirect_stderr = on log_line_prefix = '[%m] %p %q<%u %d %r> ' stats_block_level = on stats_row_level = on autovacuum = on autovacuum_naptime = 10s autovacuum_vacuum_threshold = 1 autovacuum_analyze_threshold = 1 datestyle = 'iso, mdy' lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' escape_string_warning = off standard_conforming_strings = on sql_inheritance = off -Kevin -- 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 #4317: problem with comparision of datatype date
>>> On Fri, Jul 18, 2008 at 2:35 PM, in message <[EMAIL PROTECTED]>, Sanjay Rajdev <[EMAIL PROTECTED]> wrote: > Thanks for the thought, I think that this can be the problem, as earlier > there was no indexing on the column, but 2 days back we added index on 5 of > columns in the table and "somedate" is one of them. This problem have been > noticed after the index's were added. > I can't test this out as this happens while people are using the software, Try adding another index on the column (with a different index name) using CREATE INDEX CONCURRENTLY. Then drop the old index. -Kevin -- 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 #4407: Bug in PQexecPrepared when using an integer primary key that does not start at 1
The following bug has been logged online: Bug reference: 4407 Logged by: Kevin Jenkins Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3.3 build1400 Operating system: Windows Description:Bug in PQexecPrepared when using an integer primary key that does not start at 1 Details: If I call PQexecPrepared from C++, it can fail incorrectly along the following lines: ERROR: insert or update on table "users" violates foreign key constraint "users _homecountryid_fk_fkey" DETAIL: Key (homecountryid_fk)=(1) is not present in table "country". Using the following table: CREATE TABLE lobby2.country ( country_id integer PRIMARY KEY NOT NULL, -- country id country_sort_id integer NOT NULL, -- display order for a list of countries... country_code character varying(2) NOT NULL, -- country 2 letters ISO code, like... country_name character varying(100) NOT NULL, -- county's full name country_has_states boolean NOT NULL DEFAULT false, -- defines if a country has a pre-defined list of states. can be TRUE or FALSE country_enable boolean NOT NULL DEFAULT true -- country enabled or disbaled, can be either true or false ) WITH (OIDS=FALSE); INSERT INTO lobby2.country (country_id, country_sort_id, country_code, country_name, country_has_states, country_enable) VALUES (120, 100, 'AF', 'Afghanistan', false, true); INSERT INTO lobby2.country (country_id, country_sort_id, country_code, country_name, country_has_states, country_enable) VALUES (121, 200, 'AL', 'Albania', false, true); // ... INSERT INTO lobby2.country (country_id, country_sort_id, country_code, country_name, country_has_states, country_enable) VALUES (355, 23700, 'US', 'United States', true, true); INSERT INTO lobby2.country (country_id, country_sort_id, country_code, country_name, country_has_states, country_enable) VALUES (356, 23800, 'UM', 'United States Minor Outlying Isl', false, true); // ... With the following statement: UPDATE lobby2.users SET homeCountryId_fk=$1::integer WHERE userId_pk=$2::integer Where $1::integer is 355 and userId_pk is 1. The exact same statement, using text instead of a parameter: UPDATE lobby2.users SET homeCountryId_fk=355 WHERE userId_pk=$2::integer Works fine. It of course also works in the pgAdmin III query browser. Adding a phony country that starts at index 1: -- Phony country INSERT INTO lobby2.country (country_id, country_sort_id, country_code, country_name, country_has_states, country_enable) VALUES (1, 1, '1', '1', false, false); Fixes the problem. -- 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 #4496: Memory leak in pg_dump.c?
>>> Francisco Olarte Sanz <[EMAIL PROTECTED]> wrote: > Similarly nearly nobody bothers > to fclose() stdin/out/err On that one, maybe it should be done more often. In writing pg_clearxlogtail I found that closing stdout improved performance markedly. This was a filter piping from disk into gzip. -Kevin -- 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 #4509: array_cat's null behaviour is inconsistent
The following bug has been logged online:
Bug reference: 4509
Logged by: Kevin Field
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.3.4
Operating system: Windows Server 2003 SP2
Description:array_cat's null behaviour is inconsistent
Details:
Section 9.2 in the docs say, 'The ordinary comparison operators yield null
(signifying "unknown") when either input is null.' This applies to other
operators too. For example, the result of tacking an unknown value onto a
known one is unknown, because you don't know what exactly you just tacked
on. So
select null::text || 'hello';
...returns NULL, which makes sense. But then this
select array_cat(null::integer[], '{3}'::integer[]);
...doesn't return NULL, which it should, for consistency.
Kev
--
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] installation bug-cannot create user name
>>> <[EMAIL PROTECTED]> wrote: > installing postgreSQL it brings up an error message that last part of > the install saying "could not create user name" and then some other > stuff about how this may affect post-install operations. People will have a hard time offering useful suggestions without more detail. What operating system? What version of PostgreSQL? How are you installing it? What is the exact and full text of the messages, including any user name? Does the user name it can't create already exist on your system? -Kevin -- 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 #4651: Postgresql connection error with PHP 5.
>>> "Nitin" wrote: > Is the server running on host "PostgreSQL" and > accepting TCP/IP connections on port 5432? This is very unlikely to be a bug, so a better list would have been general or admin. You probably haven't configured connections properly for your intended use. Start with this page: http://www.postgresql.org/docs/8.3/interactive/runtime-config-connection.html Note that the default value for listen_addresses doesn't allow TCP/IP connections. -Kevin -- 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] Database/Table Owner Question
>>> wrote: > We have a lot of test databases with multiple db_owners, but very few > superusers, and table_owners switch all the time. A quick, untested idea: Create a table_owner role. Create your users with NOINHERIT and GRANT table_owner to them as appropriate. REVOKE CREATE ON SCHEMA public FROM public. GRANT CREATE ON SCHEMA PUBLIC TO table_owner. A user would need to SET ROLE table_owner to create a table. RESET ROLE would put them back to normal. Just a thought -Kevin -- 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 with function returning composite types.
>>> Kyle Butt wrote: > select (bug_function()).*; > psql:sql/bug_example.sql:32: NOTICE: in bug_function > psql:sql/bug_example.sql:32: NOTICE: in bug_function > psql:sql/bug_example.sql:32: NOTICE: in bug_function > psql:sql/bug_example.sql:32: NOTICE: in bug_function > psql:sql/bug_example.sql:32: NOTICE: in bug_function > psql:sql/bug_example.sql:32: NOTICE: in bug_function > psql:sql/bug_example.sql:32: NOTICE: in bug_function > psql:sql/bug_example.sql:32: NOTICE: in bug_function > psql:sql/bug_example.sql:32: NOTICE: in bug_function > psql:sql/bug_example.sql:32: NOTICE: in bug_function > a | b | c | d | e | f | g | h | i | j > ---+---+---+---+---+---+---+---+---+--- > 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 > (1 row) For completeness: cir=# select * from bug_function(); NOTICE: in bug_function a | b | c | d | e | f | g | h | i | j ---+---+---+---+---+---+---+---+---+--- 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 (1 row) -Kevin -- 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 #4730: Vacuum full verbose analyze "deadlock"
>>> "Wayne Conrad" wrote: > "VACUUM FULL ANALYZE VERBOSE" on a "deadlocks" > "VACUUM VERBOSE ANALYZE" (without the "FULL") does not You do realize that FULL should not be part of normal maintenance, right? It is sometimes useful to recover from table bloat when normal maintenance fails. Although it is almost always much slower than CLUSTER, it has the advantage of not requiring disk space for a second copy of the table, but it requires a REINDEX afterward to correct the index bloat it causes. If you are doing a good job of normal maintenance, you never, ever should be running VACUUM FULL. None of the above means you haven't found a problem worth looking at -- I'm not trying to comment on that; but unless you are in the middle of recovery from abnormal bloat, you may be able to dodge the problem by correcting your maintenance practices. -Kevin -- 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 #4730: Vacuum full verbose analyze "deadlock"
Wayne Conrad wrote: > the database started getting slow over time. As Alvaro pointed out, this can happen if your fsm configuration doesn't allow enough space for a normal VACUUM to keep track of all the free space. Since you're running VACUUM with the VERBOSE option, be sure to capture the output and review the last few lines; this will tell you what setting you have and the minimum which would cover current needs. Also, be sure you haven't disabled autovacuum along the way. There are some usage patterns which require special forms of maintenance, but VACUUM FULL usually isn't the best option for routine maintenance. If you notice performance degrading again, please post details on the performance list. I hope this helps. -Kevin -- 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 #4763: postgres service unstable, even during install
The following bug has been logged online: Bug reference: 4763 Logged by: Kevin Field Email address: k...@brantaero.com PostgreSQL version: 8.4-beta1 Operating system: Windows Server 2003 Standard Edition SP2 Description:postgres service unstable, even during install Details: Using http://www.postgresql.org/ftp/binary/v8.4beta/win32/postgresql-8.4-beta1.zip on a console mode session with SAV autoprotect disabled (and besides told to exclude anything below "C:\Program Files\PostgreSQL\"), three times now I've tried to install (no stack builder, then adminpack+fuzzystring+pldebugger and plpgsql+plperl+plperlu) and encountered the following problems. The installer gets to the step "Activating procedural languages..." and an error window pops up: Database command error: server closed the connection unexpectedly [square character]This probably means the server terminated abnormally [square character]before or while processing the request ...and indeed, the service is not running. If I follow along with the Services window open during install, at one point the new service shows up and has status "Starting..." but I'm not sure if it fails to start or if it starts and then the procedural language thing makes it bail. I OK the box and another one comes: "Failed to enable procedural language plperl. The files are installed but are not activated in any database." And another: "Database command error: no connection to the server" Then repeat the last two for "plperlu (untrusted)". Then: "Failed to connect to the 'template1' database. Contrib files are installed, but are not activated in any databases." ...and then finally "Could not connect to server" and asking whether it's accepting connections. If after the very first error message I go into Services and start it manually, the last two messages here do not come up, but the rest still do. Then I get a success message. However, after that when I try to run a script to dump another database and restore it onto the beta, I get a "file not found" error, which is really odd both because it was working fine on the 2009-03-24 build and because the permissions have not changed. Aside from that, which is it's own problem, after that error the service is no longer running and has to be started again. This makes me think that something similar happens during the install, so that something fails with the plperl setup that causes the service to shut down (rather than it failing to start up in the first place.) Anyway both the 2009-01-01 and 2009-03-24 builds seemed smoother to set up and much more stable. Not sure why that would be... Thanks, Kev -- 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 #4763: postgres service unstable, even during install
> However, after that when I try to run a script to dump another database and > restore it onto the beta, I get a "file not found" error, which is really > odd both because it was working fine on the 2009-03-24 build and because the > permissions have not changed. Aside from that, which is it's own problem, > after that error the service is no longer running and has to be started > again. > > This makes me think that something similar happens during the install, so > that something fails with the plperl setup that causes the service to shut > down (rather than it failing to start up in the first place.) If I shut down the 8.4-beta1 service and start up the 2009-03-24 service on the same port and run the script the same way (it's a perl script I'm accessing through a browser), it runs fine, so I can't see how it would be an Apache or Perl permissions issue (i.e., what those two are running as)--I just hit F5, it's launching the same pg_restore binary from the same account on the same restore file. The one way is fine, the other says, "No such file or directory" (sorry: not "file not found," that was a bit imprecise of me earlier.) I found I was looking in the wrong log for more detail, but I found some: pg_restore: WARNING: database "production" does not exist pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 519; 2612 47275 PROCEDURAL LANGUAGE plperl mysuperuser pg_restore: [archiver (db)] could not execute query: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. Command was: CREATE PROCEDURAL LANGUAGE plperl; After that it's an error with every statement because there's no connection to the server. Now the really weird thing is, "production" doesn't exist on my 2009-03-24 server either. That's the one the dump is *from*, but I'm using "pg_restore -d dev", so it shouldn't matter that it doesn't exist (and indeed it doesn't on the 2009-03-24 server, because that works fine, note that I'm even using the 8.4-beta1 pg_restore binary for both cases.) And for the record the script drops "dev" and creates it from template0 right before trying to restore over it. Is it possible it's looking for Perl in the wrong place? (Hence the "No such file..." error that somehow makes it back to my Perl script?) Kev -- 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 #4763: postgres service unstable, even during install
On Apr 23, 12:13 pm, dp...@pgadmin.org (Dave Page) wrote: > On Thu, Apr 23, 2009 at 4:30 PM, Kevin Field > wrote: > > Is it possible it's looking for Perl in the wrong place? (Hence the > > "No such file..." error that somehow makes it back to my Perl script?) > > What version of perl do you have? I have 5.8.8 in C:\Perl and 5.10.0 in C:\Perl5.10 But no problems with 2009-03-24...aren't both that and 8.4-beta1 linked against 5.8.8? -- 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 #4763: postgres service unstable, even during install
On Apr 23, 11:30 am, Kevin Field wrote: > > pg_restore: WARNING: database "production" does not exist > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 519; 2612 47275 > PROCEDURAL LANGUAGE plperl mysuperuser > pg_restore: [archiver (db)] could not execute query: server closed the > connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > Command was: CREATE PROCEDURAL LANGUAGE plperl; Just for yucks, I tried creating the database 'production' (despite the fact that that shouldn't make a difference) and re-running the script, and it gave the same error minus the first line. -- 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 #4763: postgres service unstable, even during install
On Apr 23, 2:25 pm, dp...@pgadmin.org (Dave Page) wrote: > On Thu, Apr 23, 2009 at 6:43 PM, Kevin Field > wrote: > > On Apr 23, 12:13 pm, dp...@pgadmin.org (Dave Page) wrote: > >> On Thu, Apr 23, 2009 at 4:30 PM, Kevin Field > >> wrote: > >> > Is it possible it's looking for Perl in the wrong place? (Hence the > >> > "No such file..." error that somehow makes it back to my Perl script?) > > >> What version of perl do you have? > > > I have 5.8.8 in C:\Perl and 5.10.0 in C:\Perl5.10 > > > But no problems with 2009-03-24...aren't both that and 8.4-beta1 > > linked against 5.8.8? > > The community installer for beta 1 uses Perl 5.10. The one-click uses > 5.8 in beta 1 and earlier snapshots. Both will use 5.10 for beta 2 (as > well as Python 2.6 and TCL 8.5). > > I'm wondering if it's barfing because it's finding the wrong version > when it tries to install pl/perl. That would also explain a report I > saw of the installer failing with a similar error. Good to know. And yay for 5.10! That's a welcome upgrade. So I need to keep my 5.8 around currently for other uses--how can I hint at the correct location for it? -- 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 #4763: postgres service unstable, even during install
On Apr 24, 8:40 am, dp...@pgadmin.org (Dave Page) wrote: > > > > So I need to keep my 5.8 around currently for other uses--how can I > > hint at the correct location for it? > > In theory, by setting the path for the server only. In practice I'm > not sure how you could do that, except by possibly modifying the > per-user path setting for the service account. Hmm...our service account is also needed for 5.8. I suppose I could make a new service account for it. Will this be solved in beta2, probably? -- 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 #4763: postgres service unstable, even during install
On Apr 24, 9:32 am, dp...@pgadmin.org (Dave Page) wrote: > > I don't know if there is any way we can solve it, except by reverting > back to 5.8 or advising users to use only one version. LOL...ah, great. Well, I'd love to move to 5.10 for both. A note in the docs would be handy either way. -- 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 #4763: postgres service unstable, even during install
On Apr 24, 9:32 am, dp...@pgadmin.org (Dave Page) wrote: > > I don't know if there is any way we can solve it, except by reverting > back to 5.8 or advising users to use only one version. I just had an idea--at least in the ActiveState distributions (not sure about Strawberry or Vanilla) they include versioned-filename binaries. So you can launch perl5.8.8.exe or perl5.10.0.exe and as long as it's in the path you get the exact right one. (As I found out when I went to try to figure out per-user paths, I somehow have both versions in the universal path.) Would that help? -- 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 #4780: Aggregate functions are unaware of LIMIT clauses in SELECTs
"Ted Holzman" wrote: > AGGREGATE functions don't appear to respond to LIMIT clauses. Not a bug. LIMIT affects how many rows are in the result set the LIMIT qualifies. > select sum(generate_series) > from generate_series(1,10) limit 3; > sum > - > 55 > (1 row) > > I was expecting the sum to be 6. The LIMIT is applied to the final result set, which is only one row, so the LIMIT has nothing to do. If you wanted to limit how many rows went into the aggregate function, you'd need to do something like this: select sum(generate_series) from (select generate_series(1,10) limit 3) x; sum - 6 (1 row) -Kevin -- 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 #4763: postgres service unstable, even during install
I found an additional message in the Application Event Log: 2009-04-17 12:05:00 GMT FATAL: could not create lock file "postmaster.pid": Permission denied -- 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] sorting problem
>>> CK Leung wrote:
> the result : select * from tt order by item_code;
>
> item_code
> --
> V
> .V
> V.
> VA.AAA
> V.B
> V
> (V
> (V)
> (V)B.BBB
> (VB)BBB
> V.
> V)
> VCCC
> (13 rows)
>
> the sort sequence like ignore the character '.' , '(', ')'. Is it a
> bug ? It is no problem in old version
Probably not a bug; many collating sequences are defined to ignore
such characters. Perhaps you chose a different locale when you
converted?
-Kevin
--
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 #4763: postgres service unstable, even during install
On Apr 26, 2:08 pm, dp...@pgadmin.org (Dave Page) wrote: > On Fri, Apr 24, 2009 at 3:09 PM, Kevin Field > wrote: > > On Apr 24, 9:32 am, dp...@pgadmin.org (Dave Page) wrote: > > >> I don't know if there is any way we can solve it, except by reverting > >> back to 5.8 or advising users to use only one version. > > > I just had an idea--at least in the ActiveState distributions (not > > sure about Strawberry or Vanilla) they include versioned-filename > > binaries. So you can launch perl5.8.8.exe or perl5.10.0.exe and as > > long as it's in the path you get the exact right one. (As I found out > > when I went to try to figure out per-user paths, I somehow have both > > versions in the universal path.) Would that help? > > No, because we use the DLLs, not the .exe. But they do seem to be > versioned anyway, so there must be something else going on :-( :( Well on my end, I haven't been able to figure out how to get it to see the right version. Do you know when the next beta or win32 installer build might be out so I can try again? -- 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 #4789: ERROR 22008 on timestamp import
>>> Tom Lane wrote: > but I bet it's the change in the default integer_datetimes setting > that is the relevant difference. Confirmed. cc=> select '1999-08-06 00:12:57.99900Z'::timestamptz; ERROR: date/time field value out of range: "1999-08-06 00:12:57.99900Z" cc=> select version(); version - PostgreSQL 8.3.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070115 (SUSE Linux) (1 row) cc=> show integer_datetimes ; integer_datetimes --- on (1 row) -Kevin -- 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 #4763: postgres service unstable, even during install
On May 1, 12:41 pm, Kevin Field wrote: > On Apr 26, 2:08 pm, dp...@pgadmin.org (Dave Page) wrote: > > > On Fri, Apr 24, 2009 at 3:09 PM, Kevin Field > > wrote: > > > On Apr 24, 9:32 am, dp...@pgadmin.org (Dave Page) wrote: > > > >> I don't know if there is any way we can solve it, except by reverting > > >> back to 5.8 or advising users to use only one version. > > > > I just had an idea--at least in the ActiveState distributions (not > > > sure about Strawberry or Vanilla) they include versioned-filename > > > binaries. So you can launch perl5.8.8.exe or perl5.10.0.exe and as > > > long as it's in the path you get the exact right one. (As I found out > > > when I went to try to figure out per-user paths, I somehow have both > > > versions in the universal path.) Would that help? > > > No, because we use the DLLs, not the .exe. But they do seem to be > > versioned anyway, so there must be something else going on :-( > > :( Well on my end, I haven't been able to figure out how to get it to > see the right version. Do you know when the next beta or win32 > installer build might be out so I can try again? Through some testing today I determined: 1) the installer decides on whether you have Perl / where it is before the first opportunity to click 'back', so, right at the beginning. 2) if I rename my Perl 5.8 directory--whether or not I rename my Perl 5.10 directory--the installer cannot find it and does not present pl/ perl[u] as an option a few Next-clicks later. 3) if I rename my Perl 5.10 directory to where I had had my Perl 5.8 directory, it's still not presented as an option So...what, is it insisting on perl58.dll at the beginning of the install and then perl510.dll when it goes to actually use it? Let's see...i'll rename things back (so it'll be an option), select the pl/ perl options, and then rename the directories before clicking 'next'. Result: same errors when it goes to activate plperl and plperlu. I guess not. -- 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 #4763: postgres service unstable, even during install
On May 2, 12:09 pm, Kevin Field wrote: > On May 1, 12:41 pm, Kevin Field wrote: > > > > > On Apr 26, 2:08 pm, dp...@pgadmin.org (Dave Page) wrote: > > > > On Fri, Apr 24, 2009 at 3:09 PM, Kevin Field > > > wrote: > > > > On Apr 24, 9:32 am, dp...@pgadmin.org (Dave Page) wrote: > > > > >> I don't know if there is any way we can solve it, except by reverting > > > >> back to 5.8 or advising users to use only one version. > > > > > I just had an idea--at least in the ActiveState distributions (not > > > > sure about Strawberry or Vanilla) they include versioned-filename > > > > binaries. So you can launch perl5.8.8.exe or perl5.10.0.exe and as > > > > long as it's in the path you get the exact right one. (As I found out > > > > when I went to try to figure out per-user paths, I somehow have both > > > > versions in the universal path.) Would that help? > > > > No, because we use the DLLs, not the .exe. But they do seem to be > > > versioned anyway, so there must be something else going on :-( > > > :( Well on my end, I haven't been able to figure out how to get it to > > see the right version. Do you know when the next beta or win32 > > installer build might be out so I can try again? > > Through some testing today I determined: > > 1) the installer decides on whether you have Perl / where it is before > the first opportunity to click 'back', so, right at the beginning. > 2) if I rename my Perl 5.8 directory--whether or not I rename my Perl > 5.10 directory--the installer cannot find it and does not present pl/ > perl[u] as an option a few Next-clicks later. > 3) if I rename my Perl 5.10 directory to where I had had my Perl 5.8 > directory, it's still not presented as an option > > So...what, is it insisting on perl58.dll at the beginning of the > install and then perl510.dll when it goes to actually use it? Let's > see...i'll rename things back (so it'll be an option), select the pl/ > perl options, and then rename the directories before clicking 'next'. > Result: same errors when it goes to activate plperl and plperlu. I > guess not. This is actually a bit worse than I thought--I can't ditch 5.8, then. I haven't tried a full uninstall of 5.8 and 5.10 and then installing 5.10 and then installing pgsql. I guess that's the next thing to try. -- 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 #4763: postgres service unstable, even during install
On May 2, 12:11 pm, Kevin Field wrote: > On May 2, 12:09 pm, Kevin Field wrote: > > > > > On May 1, 12:41 pm, Kevin Field wrote: > > > > On Apr 26, 2:08 pm, dp...@pgadmin.org (Dave Page) wrote: > > > > > On Fri, Apr 24, 2009 at 3:09 PM, Kevin Field > > > > wrote: > > > > > On Apr 24, 9:32 am, dp...@pgadmin.org (Dave Page) wrote: > > > > > >> I don't know if there is any way we can solve it, except by reverting > > > > >> back to 5.8 or advising users to use only one version. > > > > > > I just had an idea--at least in the ActiveState distributions (not > > > > > sure about Strawberry or Vanilla) they include versioned-filename > > > > > binaries. So you can launch perl5.8.8.exe or perl5.10.0.exe and as > > > > > long as it's in the path you get the exact right one. (As I found out > > > > > when I went to try to figure out per-user paths, I somehow have both > > > > > versions in the universal path.) Would that help? > > > > > No, because we use the DLLs, not the .exe. But they do seem to be > > > > versioned anyway, so there must be something else going on :-( > > > > :( Well on my end, I haven't been able to figure out how to get it to > > > see the right version. Do you know when the next beta or win32 > > > installer build might be out so I can try again? > > > Through some testing today I determined: > > > 1) the installer decides on whether you have Perl / where it is before > > the first opportunity to click 'back', so, right at the beginning. > > 2) if I rename my Perl 5.8 directory--whether or not I rename my Perl > > 5.10 directory--the installer cannot find it and does not present pl/ > > perl[u] as an option a few Next-clicks later. > > 3) if I rename my Perl 5.10 directory to where I had had my Perl 5.8 > > directory, it's still not presented as an option > > > So...what, is it insisting on perl58.dll at the beginning of the > > install and then perl510.dll when it goes to actually use it? Let's > > see...i'll rename things back (so it'll be an option), select the pl/ > > perl options, and then rename the directories before clicking 'next'. > > Result: same errors when it goes to activate plperl and plperlu. I > > guess not. > > This is actually a bit worse than I thought--I can't ditch 5.8, then. > I haven't tried a full uninstall of 5.8 and 5.10 and then installing > 5.10 and then installing pgsql. I guess that's the next thing to try. Okay, I uninstalled both 5.8 and 5.10, installed 5.10 fresh, and it doesn't detect it during install. I tried another fresh install of 5.10 with PerlScript. Same deal. Now I'm trying a fresh install with all options checked. Still no dice. What exactly is it looking for? Has anybody had a successful install of ActiveState Perl 5.10 build 1004 followed by PostgreSQL 8.4-beta1 with plperl[u]? -- 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 #4763: postgres service unstable, even during install
On May 8, 9:11 am, Kevin Field wrote: > On May 4, 2:10 pm, dp...@pgadmin.org (Dave Page) wrote: > > > The installer is still looking for perl58.dll, whilst the server > > needs perl510.dll. I've committed a fix for that (and the other PLs > > which were similarly afflicted). > > > I've rebuilt the installer (using the existing binaries from the last > > build) and uploaded it tohttp://developer.pgadmin.org/~dpage/. Please > > give it a whirl. > > Thanks for the rebuild. I finally got to try it today, but it was the > same story (at least, without renaming folders and such): can't > install plperl, then can't even find the service running to try > plperlu; after the install, I start the service again and run my > script and at the point at which it would run pg_restore, that fails > and the service is no longer running. The new 8.4-beta2 seems to be behaving the exact same way. :( -- 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 #4763: postgres service unstable, even during install
On May 22, 9:28 am, Kevin Field wrote: > On May 8, 9:11 am, Kevin Field wrote: > > > > > On May 4, 2:10 pm, dp...@pgadmin.org (Dave Page) wrote: > > > > The installer is still looking for perl58.dll, whilst the server > > > needs perl510.dll. I've committed a fix for that (and the other PLs > > > which were similarly afflicted). > > > > I've rebuilt the installer (using the existing binaries from the last > > > build) and uploaded it tohttp://developer.pgadmin.org/~dpage/. Please > > > give it a whirl. > > > Thanks for the rebuild. I finally got to try it today, but it was the > > same story (at least, without renaming folders and such): can't > > install plperl, then can't even find the service running to try > > plperlu; after the install, I start the service again and run my > > script and at the point at which it would run pg_restore, that fails > > and the service is no longer running. > > The new 8.4-beta2 seems to be behaving the exact same way. :( I tried again just now, renaming my Perl 5.8 directory before installing so it wouldn't find it, but it still behaved the exact same way. Then I tried running 'create language plperl' from pgAdmin3 and all it said was ' ERROR *' and shut down the entire PostgreSQL service. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] [BUGS] Cursor with hold emits the same row more than once across commits in 8.3.7
Tom Lane wrote: > Robert Haas writes: >> This seems like the only option that will produce correct answers, >> so it gets my vote. How much is the performance penalty for >> materializing the tuplestore? I'm inclined to think that whatever >> it is, you just have to pay it if you ask for a WITH HOLD cursor. > > I don't mind paying it for a WITH HOLD cursor, since by definition > you're asking for a more expensive behavior there. The thing that > is bothering me more is whether we want to pay a price for a *non* > WITH HOLD cursor. You can get instability for seqscan or volatile > functions if you just try MOVE BACKWARD ALL and re-read. I would expect to pay more for a scrollable cursor than non- scrollable; and in fact, the fine manual says "Depending upon the complexity of the query's execution plan, specifying SCROLL might impose a performance penalty on the query's execution time." That would tend to argue in favor of taking the time to produce correct answers. It does raise a question, though, about another sentence in the same paragraph: "The default is to allow scrolling in some cases; this is not the same as specifying SCROLL." Either we make people pay for this when they haven't specified SCROLL but PostgreSQL has historically given it to them anyway, or we might break existing applications. -Kevin -- 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 #4849: intermittent future timestamps
David Leppik wrote: > Never mind. Turns out the bug was in our own code (read: me, > personally, being stupid) to convert a java.sql.Timestamp to > java.sql.Date. Why it works at all in MySQL... I don't even want > to know. java.sql.Date or java.util.Date? (You don't show your imports, so it's impossible to tell from the code snippet.) If it's java.util.Date, I can't immediately see why your errors would be greater than one second. If it's java.sql.Date, you're probably in territory where the behavior is undefined, but it's hard to see where you would get the results you showed. Perhaps there's an opportunity for us to make the PostgreSQL JDBC driver behave more sanely in this circumstance? > Why is it we can spend weeks looking at a bug, and we can't find > it until we decide to blame it on someone else? It's probably a corollary to the tendency to see our own gaffs when reading the post coming back from the list much more clearly than they appeared before clicking "send". :-/ -Kevin -- 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] unhelpful error message
Tom Lane wrote: > Per the fine manual, sp.count is another way of writing count(sp). Wow, that seems a horrid kludge. Is the standard responsible for that one, or is it a PostgreSQL extension? Could you point me at where in the fine manual this is covered? I've never stumbled across it in the many hours I've spent in reading the manual. A search didn't help much. I poked around all the relevant sections I could think of without success. I even resorted to: find -name '*.sgml' | xargs grep -i \\.count which only kicked out: ./doc/src/sgml/ltree.sgml: Example: Top.Countries.Europe.Russia -Kevin -- 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] unhelpful error message
Tom Lane wrote: > Look under "computed fields" in the index ... looks like it's > towards the bottom of 34.4.2 in the 8.3 docs. > http://www.postgresql.org/docs/8.3/static/xfunc-sql.html#AEN40267 > > I had thought it was mentioned somewhere in chapter 4 as well, but > am not seeing it there right now. It's used in an example in 34.4.2 without a lot of definition. From experimenting a bit, it appears that when referencing a composite data value, any function which can take as its only parameter an instance of that composite type can be used as though it were a field name. This includes user functions written in any language, as well as built-in aggregates (and presumably any other functions which accept a composite type as the only parameter). Is that correct? Any restrictions or exceptions? (I assume that they are only allowed to retrieve values -- it doesn't seem like it would make sense to SET a value into such a "computed field".) It's clearly not particular to SQL functions, so it deserves mention outside of the context you referenced. Chapter 4 does seem like a good place. Under Column References or Function Calls (or both)? -Kevin -- 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 #4870: don't start service
"Luis angel camacho" wrote: > the service can't start How are you starting it? What error messages, if any, do you see? (Copy/paste if possible; failing that, please give exact text.) What is in the logs from around the time of the failed attempt to start the service? -Kevin -- 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 #4908: escaping and dollar quotes: "ERROR: unterminated string"
Tom Lane wrote: > Or you could turn on standard_conforming_strings if you'd prefer not > to deal with escapes. That doesn't help with this, because of the separate pgpgsql parser: ccdev=> select version(); version - PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070115 (SUSE Linux) (1 row) ccdev=> show standard_conforming_strings ; standard_conforming_strings - on (1 row) ccdev=> create or replace function temp() returns text language plpgsql AS $$ begin return '\'; end; $$; ERROR: unterminated string CONTEXT: compile of PL/pgSQL function "temp" near line 2 -Kevin -- 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 #4914: uuid_generate_v4 not present in either source or yum/rpm
David Kerr wrote: > I'm working on my management to allow me to roll my own PG and get a > 3rd party support. FWIW, we're a SLES shop, and we've found it best to build our own. -Kevin -- 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 4906?
Mathieu Fenniak wrote: > I entered a bug report yesterday through the PostgreSQL web site > that was assigned bug ID 4906. However, looking through the > pgsql-bugs list, I don't see the posting I entered -- is it possible > this bug report disappeared into the void? Should I resubmit it? Apparently so. There is often a delay, due to the need for review to prevent spam from getting through; but sometimes things seem to just disappear. If it hasn't shown up by now, it's probably not going to do so. -Kevin -- 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 #4960: Unexpected timestamp rounding
"Matthias" wrote: > I noticed an unusual (and from my point of view inconsistent) > rounding of a timestamp: What do you get when you run?: show integer_datetimes; If it is off, which is probably the default for your distribution under 8.3.X, timestamps are floating point (approximate) values which get less precise as you move away from the base timestamp of '2000-01-01 00:00'. The default under 8.4 is to use integer timestamps, which have a microsecond precision across the range they support. (That range is not as broad as the floating point format, but plenty large for most practical uses.) You can configure PostgreSQL to use integer timestamps in 8.3 if you build from source, but you will need to convert your database. -Kevin -- 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 #4960: Unexpected timestamp rounding
"Matthias" wrote: > It is about when using a upper-boundary timestamp. The value of > -12-31 23:59:59.99 is sometimes used to indicate an infinite > validity. One other thought -- using a "magic value" for something like this is usually a bad idea. NULL indicates the absence of a value, and means "unknown or not applicable". I generally use that for an upper bound when there is no valid upper bound. In particular, expiration or end dates which will probably eventually be fixed, but haven't been yet, are more appropriately NULL. It isn't that there won't be one; it just isn't known yet -- which fits the semantics of NULL very well. -Kevin -- 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 #4963: Selecting timestamp without timezone at timezone gives wrong output
"William Crawford" wrote: > set time zone 'US/Eastern'; > select > timestamp '2009-01-01', > timestamp '2009-01-01' at time zone 'US/Pacific' >as withouttimezone, > timestamp with time zone '2009-01-01' at time zone 'US/Pacific' >as withtimezone; > > timestamp |withouttimezone |withtimezone > -++- > 2009-01-01 00:00:00 | 2009-01-01 03:00:00-05 | 2008-12-31 21:00:00 > (1 row) > > I expect the last 2 values to be the same. If you tilt your head just right, these make sense. The withouttimezone column sees "timestamp '2009-01-01'" and takes that as a timestamp without time zone. Since it has no association with any time zone, it doesn't yet represent any moment in time. Then you say you want to associate that abstract notion with the Pacific time zone, so it does, and it becomes a timestamp with time zone reflecting '2009-01-01 00:00:00' in the Pacific time zone. Then you display it without specifying the time zone in which to view it, so it shows it in your time zone, which is three hours later by your local clock. The withouttimezone column sees the literal in your local time and calculates what the clock would say in the Pacific time zone at that moment. Timestamp without time zone is generally both useless and dangerous. -Kevin -- 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 #4963: Selecting timestamp without timezone at timezone gives wrong output
"Kevin Grittner" wrote: > The withouttimezone column sees the literal in your local time and s/withouttimezone/withtimezone/ -Kevin -- 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 #4966: wrong password.....
"walkerlacombe" wrote: > PostgreSQL version: 8.0 > Operating system: vista home premium While Alvaro has very kindly added something to the "Frequently Asked Questions" (FAQ) page which might help you: http://wiki.postgresql.org/wiki/FAQ#I_lost_the_database_password.__What_can_I_do_to_recover_it.3F you should be aware that the above is not a supported environment. Note that on the Windows download page: http://www.postgresql.org/download/windows It says, "Only PostgreSQL 8.2 and above are supported on Windows." It might be best to go to 8.3 or 8.4, though. Each new major version contains significant improvements. -Kevin -- 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 #5011: Standby recovery unable to follow timeline change
"James Bardin" wrote: > Is this currently possible, or do I have to send a full file-level > backup to sync the ex-master server with the new master? I believe you have to get a new base backup from the new master to the new standby. Consider rsync, which might do it *really* fast if not much has changed yet. -Kevin -- 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] inconsistent composite type null handling in plpgsql out variable
Merlin Moncure wrote: > This leads to some very weird behaviors, for example 'coalesce(foo, > something)' and 'case when foo is null then something else foo end' > can give different answers. Quite apart from the issue you're pursuing, this is another example of how the COALESCE predicate in PostgreSQL is not compliant with the standard, where it is *defined as* an abbreviation of the CASE predicate. I might be persuaded otherwise by a reference to the standard, but my understanding is that the CASE predicate should be conceptually similar to the "? :" predicate in C. Does anyone else feel that these aren't implemented quite right in PostgreSQL? -Kevin -- 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 #5023: pg_relation_size() is not case sensitive
"Joseph Shraibman" wrote:
> The pg_relation_size(text) method cannot determine the size of a
> relation that has capital letters.
Did you try putting quotes inside the apostrophes?:
SELECT pg_relation_size('"MixedCaseRelation"');
-Kevin
--
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 #5023: pg_relation_size() is not case sensitive
Joseph Shraibman wrote: > Kevin Grittner wrote: >> Did you try putting quotes inside the apostrophes?: >> > No, I didn't. I didn't know I could do that. That's generally true in recent versions. (Try the -t option on pg_dump for the first place I ran into it.) Perhaps it should be documented better, but it doesn't seem like a good idea to put it everywhere a relation name is referenced. I wonder if it would be overkill to have a short page on the topic in the documentation and just link to it from each documentation page where it might be needed -Kevin -- 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 #5028: CASE returns ELSE value always when type is "char"
Tom Lane wrote: > Joseph Shraibman writes: >> So the type of what is in the ELSE clause determines the type of >> the output? > > If all the other branches are unknown literals, yes. What's the best place to look to get a handle on what the benefits are of treating character string literals as being of unknown type? (Other than the obvious backward compatibility issues.) -Kevin -- 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 #5028: CASE returns ELSE value always when type is "char"
Tom Lane wrote: > "Kevin Grittner" writes: >> Tom Lane wrote: >>> Joseph Shraibman writes: >>>> So the type of what is in the ELSE clause determines the type of >>>> the output? >>> >>> If all the other branches are unknown literals, yes. > >> What's the best place to look to get a handle on what the benefits >> are of treating character string literals as being of unknown type? >> (Other than the obvious backward compatibility issues.) > > I think the odds of changing that are not measurably different from > zero. I figured that; I'm just trying to understand what seems to me like an odd wart on the type system. I figure I must be missing something important, so I'd kinda like to find out what that is. -Kevin -- 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 #5028: CASE returns ELSE value always when type is"char"
Jeff Davis wrote: > On Tue, 2009-09-01 at 13:49 -0500, Kevin Grittner wrote: >> I figured that; I'm just trying to understand what seems to me like >> an odd wart on the type system. I figure I must be missing >> something important, so I'd kinda like to find out what that is. > > If I understand your question, you're comparing: > > (a) leaving a literal as "unknown" until you've finished > inferring types (current behavior) > (b) casting every unknown to text immediately, and then trying to > infer the types No, that's not it. I'm wondering why it isn't treated as text. Period. Full stop. Nothing to infer. Anywhere that we have implicit casts defined from text to something else could, of course, still operate; but it would be text. No guessing. > In general, option (b) eliminates information that might be useful > for making good inferences about the correct operators to use, and > also finding cases of ambiguity. It often seems to have the opposite effect. See the original post. > For instance, consider the current behavior: > > 1. select now()::text < 'January 01, 2009'; -- true > 2. select now() < 'January 01, 2009'; -- false > 3. select now() < 'January 01, 2009'::text; > ERROR: operator does not exist: timestamp with time zone < > text In my view, it is wrong that any of those work. I would expect to have to code one of these: select now() < date '2009-01-01'; -- implicit casts should cover select now() < timestamp with time zone '2009-01-01 00:00:00.0'; I understand that there is probably a huge base of existing code which counts on being able to be sloppy with types and have PostgreSQL automagically infer types other than what is actually specified; but I'd rather not expose such sloppy behavior to those running ad hoc queries at my site. > Example #2 shows that we can infer the the RHS is of type > timestamptz based on the type of the LHS. That's desirable behavior > in any type-inferencing system -- without it you might as well just > explicitly cast all literals. Example #3 is ambiguous: we have no > way to know whether to choose "< (timestamptz, timestamptz)" or "< > (text, text)", and an ERROR is desirable behavior to avoid confusing > results. Here I think you have answered my question. It is seen as a feature, since it allows people to avoid the extra keystrokes of coding type-specific literal values, and allows them the entertainment of seeing how the values get interpreted. :-) > But you can't have both of those desirable behaviors Whether they are desirable is the point of disagreement. At least I now understand the reasoning. Thanks, -Kevin -- 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 #5028: CASE returns ELSE value always when type is"char"
Tom Lane wrote: > "Kevin Grittner" writes: >> No, that's not it. I'm wondering why it isn't treated as text. >> Period. Full stop. Nothing to infer. > > Because then we would have to provide implicit casts from text to > everything else, which would be horribly dangerous. I would like that even less. I like errors on type conflicts. >> In my view, it is wrong that any of those work. I would expect to >> have to code one of these: > >> select now() < date '2009-01-01'; -- implicit casts should cover >> select now() < timestamp with time zone '2009-01-01 00:00:00.0'; > > The current design is a compromise between usability and strictness > of semantics. This proposal appears to be all strictness and no > usability. I was not proposing anything; I was trying to understand the reasons for the current behavior so that I could think about what might make sense to address some of the places where current behavior causes a result which is different from a non-error result should be obtained under the standard. I couldn't begin to anticipate what might be acceptable in these situations without understanding the reason things are as they are. I do understand that there will be "convenience" extensions to the standard -- all products do that. I wasn't sure whether that was the reason for the behavior or whether there was something else in play. Thanks for clarifying, -Kevin -- 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 #5028: CASE returns ELSE value always when typeis"char"
Jeff Davis wrote: > I disagree that using implicit casts to make up for a lack of an > "unknown" type will improve matters I certainly never meant to imply that additional implicit casts should be added. I apologize for not being more clear about that. Thanks again for helping fill in the blanks. -Kevin -- 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 #5028: CASE returns ELSE value always when type is"char"
Tom Lane wrote: > It's interesting that you want to go in 100% the opposite direction > from Kevin, who seems to want to eliminate type inference > altogether. Maybe our current compromise isn't too bad, if it makes > everybody unhappy in opposite directions ;-) Well, it's probably worth noting that, while I would prefer strong typing all around, I recognize that unless that's easier than I think to make as a configuration option, and strict is not the default, it's not going to happen. Too many people prefer things the other way. What I'm most concerned about are the corner cases where strict typing would give one non-error result and the inferred typing results in an error or a different result from the strict typing. I'm willing to argue that those are bugs, at least when the strongly typed behavior is mandated by the SQL standard. I pray that I never again have to deal with a database as generous with typing as Pick or Advanced Revelation. Seriously, working with those gave me that feeling you normally only get during a nightmare. :-( When a "date of birth" column can contain a list of phone numbers -- well, you want just want do something that makes you forget you saw that -Kevin -- 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 #5028: CASE returns ELSE value always when type is"char"
Sam Mason wrote: > If we did follow Kevin's request directly, should we also be > specifying the type of NULL? I don't *think* the SQL standard requires that, and barring that I don't see any compelling reason to type NULL. One problem I do see with the current scheme, however, is that NULL *does* get typed to text when it makes no sense. In my view, a CASE expression which has only NULL for its return values, or an abbreviated form of CASE, such as COALESCE or NULLIF, should be evaluated exactly the same as if they were replaced by NULL itself. For example, COALESCE(NULL, NULL) currently yields NULL::text. In my view that's wrong. I view it as a bug, but that seems to be a hard sell here. Likewise, I think that in the query which started this thread, the cast to "char" is not sensible. I'm not sure how that could be resolved, but it doesn't seem correct to me. -Kevin -- 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 #5028: CASE returns ELSE value always when type is"char"
Tom Lane wrote:
> "Kevin Grittner" writes:
>> What I'm most concerned about are the corner cases where strict
>> typing would give one non-error result and the inferred typing
>> results in an error or a different result from the strict typing.
>> I'm willing to argue that those are bugs, at least when the
>> strongly typed behavior is mandated by the SQL standard.
>
> Are there any such cases? Your interpretation of strict typing
> seems to be that everything is type-labeled to start with, which
> means that type inference doesn't actually have anything to do.
A simple, self-contained example derived from the OP:
test=# create table t (c "char");
CREATE TABLE
test=# insert into t values ('a');
INSERT 0 1
test=# select case when c = 'a' then 'Hey' else c end from t;
c
---
H
(1 row)
test=# select case when c = 'a' then 'Hey'::text else c end from t;
c
-
Hey
(1 row)
-Kevin
--
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 #5028: CASE returns ELSE value always when type is"char"
I wrote:
> A simple, self-contained example derived from the OP:
>
> test=# create table t (c "char");
> CREATE TABLE
> test=# insert into t values ('a');
> INSERT 0 1
> test=# select case when c = 'a' then 'Hey' else c end from t;
> c
> ---
> H
> (1 row)
>
> test=# select case when c = 'a' then 'Hey'::text else c end from t;
> c
> -
> Hey
> (1 row)
And I'm not even sure how I'd explain the rules to someone. (I guess
that's because I don't understand them, really, but the other way
sounds better)
test=# drop table t;
DROP TABLE
test=# create table t (c varchar(2));
CREATE TABLE
test=# insert into t values ('a');
INSERT 0 1
test=# select case when c = 'a' then 'Hey' else c end from t;
c
-
Hey
(1 row)
test=# select case when c = 'a' then 'Hey'::text else c end from t;
c
-
Hey
(1 row)
-Kevin
--
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 #5028: CASE returns ELSE value always when type is"char"
Sam Mason wrote: > you were requiring the types of literals that happened to be > enclosed in quotes to have their type ascribed, so why not the NULL > literal? Well, unless things have changed in recent versions of the standard and I've missed the change, a series of characters enclosed in apostrophes is what the standard calls a "character string literal" and defines it to be be related to character based types such as varchar. As far as I'm aware, considering it to be undefined is a PostgreSQL extension. If you can point to something in the standard to show where I'm mistaken, I'll look it over. I'll go looking for something to back my memories on the topic, too, since my memory seems to be less reliable than it once was. -Kevin -- 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 #5028: CASE returns ELSE value always when type is"char"
"Kevin Grittner" wrote:
> Well, unless things have changed in recent versions of the standard
> and I've missed the change, a series of characters enclosed in
> apostrophes is what the standard calls a "character string literal"
> and defines it to be be related to character based types such as
> varchar.
That still seems to be the case in the draft of the 2003 standard I
have:
::=
|
|
|
|
|
|
::=
[ ]
[ ... ]
[ { [ ... ]
}... ]
The ball's in your court to show something in the standard to say that
a character string literal is ever *not* to be taken as a character
string.
-Kevin
--
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 #5028: CASE returns ELSE value always when type is"char"
Tom Lane wrote: > "Kevin Grittner" writes: >> And I'm not even sure how I'd explain the rules to someone. > > text is preferred to "char" which is preferred to unknown. > > This particular example would be less confusing if 'Hey'::"char" > threw an error, but that behavior is the result of an ancient > (bad?) decision in the input function of one legacy datatype. > It's not, IMNSHO, evidence of an overall failure of the type system > as a whole. So the behavior of the "char" type is anomalous in this regard? Other character-based types behave like varchar (which has the behavior I would expect here)? That is encouraging. Why isn't the behavior of "char" in this regard considered a bug to be fixed? I'm not sure I'm exactly understanding why the varchar(2) worked, though. Perhaps it would be more clear if I grasped why *that* one does what I would think is the right thing. At this point my first guess would be that it discards the length for a varchar, and just treats it as text (or some other "neutral" character-based type). If so, perhaps "char" should do the same? -Kevin -- 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 #5028: CASE returns ELSE value always when type is"char"
Sam Mason wrote: > I'd always thought '2001-01-01' was a valid date literal, seems the > standard has required it to be prefixed by DATE at least back to > SQL92. Yep. I don't know if it would be remotely feasible, but the implementation which seems like it would be "standard-safe" but still give reasonable concessions to those wanting to skip the extra keystrokes of declaring the type of literals which are not character based would be to go with the suggestion of having a character string literal type, and change the semantics such that if there is a valid interpretation of the statement with the character string literal taken as text, it should be used; if not, resolve by current "unknown" rules. Probably not feasible, but it seems likely it would make everyone reasonably happy if it could be done. That leaves the issue of NULL being forced to type text in the absence of any type info in CASE, COALESCE, and NULLIF. If there were a way to say that these could return unknown type, that would be solved. That doesn't seem as though it would be likely to be massively difficult, although I could be wrong about that. -Kevin -- 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 #5028: CASE returns ELSE value always when type is"char"
Sam Mason wrote: > CREATE VIEW v (c) AS > SELECT NULL; > > PG allows it, but the resulting view seems somewhat unusable. I'm not sure whether the only place the standard doesn't require a cast is on assignment, but this is one place that the standard clearly does require a cast, and I'm all for that. Requiring a cast anywhere else the standard requires it would not offend me; although not requiring it anywhere it doesn't generate nonstandard results, and where the semantics are relatively sane, wouldn't offend me, either. -Kevin -- 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 #5028: CASE returns ELSE value always when type is"char"
Tom Lane wrote: > "Kevin Grittner" writes: >> Yep. I don't know if it would be remotely feasible, but the >> implementation which seems like it would be "standard-safe" but >> still give reasonable concessions to those wanting to skip the >> extra keystrokes of declaring the type of literals which are not >> character based would be to go with the suggestion of having a >> character string literal type, and change the semantics such that >> if there is a valid interpretation of the statement with the >> character string literal taken as text, it should be used; if not, >> resolve by current "unknown" rules. > > There is already a weak preference for resolving unknown as text in > the presence of multiple alternatives. So I'm not sure that you're > suggesting anything different from what happens now. In particular, > weren't you the same person complaining a moment ago about > COALESCE(NULL,NULL) defaulting to text? Why is that bad if the > above is good? Because COALESCE(NULL, NULL) has given no indication that it is character based, while 'x' is defined by the standard to be a character string literal. The two uses of "unknown" in typing seem to be solving different problems. Perhaps using the same flag for both is part of the problem. The above was suggesting we differentiate. -Kevin -- 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 #5028: CASE returns ELSE value always when type is"char"
Sam Mason wrote: > On Wed, Sep 02, 2009 at 02:59:54PM -0500, Kevin Grittner wrote: >> Sam Mason wrote: >> > CREATE VIEW v (c) AS >> > SELECT NULL; >> > >> > PG allows it, but the resulting view seems somewhat unusable. >> >> I'm not sure whether the only place the standard doesn't require a >> cast is on assignment, but this is one place that the standard >> clearly does require a cast, and I'm all for that. > > I'm probably missing something obvious again, but where does it say > that? Bear in mind that my simple NULL could be an arbitrarily > complex expression, I've just chosen a simple NULL for pedagogic > reasons. > > I can only see a requirement that the chosen type must be > compatible. That seems to leave it open to arbitrarily choosing any > type in this case. I'll look. On reflection, I based that statement on the fact that our SQL parser requires it, and it was largely created by scanning in the SQL syntax rules, using OCR on the image, and sed to format them for ANTLR. That doesn't guarantee that we didn't add that particular requirement for the sake of our own sanity some time later. I doubt I can get to looking through the standard for it today, though. By the way, the case of a bare NULL literal is potentially very different from a complex expression which might resolve to NULL. The latter would generally have some result type which could be determined even in tha absence of an actual value. For example, a null-capable column clearly has a type which can be determined. -Kevin -- 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 #5028: CASE returns ELSE value always when type is"char"
Tom Lane wrote: > "Kevin Grittner" writes: >> go with the suggestion of having a character string literal type, >> and change the semantics such that if there is a valid >> interpretation of the statement with the character string literal >> taken as text, it should be used; if not, resolve by current >> "unknown" rules. > > There is already a weak preference for resolving unknown as text in > the presence of multiple alternatives. So I'm not sure that you're > suggesting anything different from what happens now. It is certainly different for the example I gave up-thread involving "char". Perhaps that is a very unique and isolated situation. -Kevin -- 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 #5028: CASE returns ELSE value always when type is"char"
Tom Lane wrote: > In a formal sense the type information available is the same, ie, > none. Well, in the sense that an international standard is formal, there is a formal difference, in that one has no type information and the other is a character string. However: > The argument that SQL says 'foo' must be character, so we should > too, is greatly weakened by the fact that SQL has such an > impoverished set of built-in types. If we want to treat > user-defined types as anything approaching first-class types, we > have to be pretty suspicious of that restriction. Another big clue for me in terms of the community perspective on this. Thanks. I think that the current approach leaves a small number of corner cases where we break SQL compliance. I think it's worthwhile trying to fix that. Whether that's best done by identifying the individual corners and fixing them independently as aberrations, or implementing some changes which provide the PostgreSQL extensions in a way that doesn't tend to break standard usage (and of course has little or no impact on current PostgreSQL users), is beyond my ken. I'm also not suggesting that this is the most urgent issue around. If anyone can suggest an appropriate wording for a TODO on the topic, I'll happily shut up and move on ;-) -Kevin -- 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 #5028: CASE returns ELSE value always when type is"char"
Tom Lane wrote: > One other point worth making is that we don't always consider SQL > compliance to be a hard requirement that trumps every other > consideration. Point noted. > An example is case-folding of identifiers; it's been pretty well > agreed that between readability and backwards-compatibility > considerations, we simply aren't going to switch over to doing it > exactly like the spec. I've left that one alone both because I saw the discussions of it and because our framework automatically fixes identifier capitalization based on our metadata and then wraps the all identifiers in quotes. As long as PostgreSQL honors the rules about quoted identifiers, we're golden. :-) (Although, one of these days I may try to do something about how psql, etc. treat identifiers which are all lower case. The refusal to consider the quoted form a match during tab-completion, for example, is a regular annoyance.) > So any proposed tweaks in this area would be considered as tradeoffs > between better spec compliance and other goals. Fair enough. I consider myself warned. ;-) -Kevin -- 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 #5028: CASE returns ELSE value always when type is "char"
Tom Lane wrote: > I certainly don't want to have "char" emulate the misbegotten > decision to have explicit and implicit coercions behave differently. > So it looks to me like the argument to make "char" work like char(1) > doesn't actually help us much to decide if an error should be thrown > here or not. On the whole, throwing an error seems better from a > usability perspective. I feel that the behavior of "char" in at least this case should match char(1) (or just plain char): test=# select case when true then 'xxx' else 'a'::"char" end from t; case -- x (1 row) test=# select case when true then 'xxx' else 'a'::char(1) end from t; case -- xxx (1 row) test=# select case when true then 'xxx' else 'a'::char end from t; case -- xxx (1 row) Much as the reason for the behavior of "char" may seem clear when inside the code looking out, it is astonishing for someone writing application code. -Kevin -- 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 #5028: CASE returns ELSE value always when type is "char"
Sam Mason wrote: > you seem to be wanting in-memory representations of "string like > types" to all use the same representation and only use the actual > types when saving to/from disk. Doing so when actually assigning to *something* of the more specific type would probably be better. In my view, that's not happening here; although I see that the code currently doesn't recognize the difference. >> test=# select case when true then 'xxx' else 'a'::"char" end from t; >> case >> -- >> x >> (1 row) > > With the patch I gave, or something like it, this would throw an > error because 'xxx' is being used to initialize a value of "char" > type. As I read the semantics of the CASE predicate, it returns one of the given values. 'x' is not one of the given values, regardless of type. I don't think an error is the right thing, I think returning the specified value is the right thing. I don't think it's a good thing that the type system decides that the result type for this case predicate is "char" and that 'xxx' needs to be coerced to that type. -Kevin -- 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 #5028: CASE returns ELSE value always when type is "char"
Sam Mason wrote:
> I fail to see how an error isn't the right thing; if we try with
> some other types let see if you think any of these should succeed.
>
> SELECT CASE WHEN TRUE THEN text 'xxx' ELSE 0 END;
> SELECT CASE WHEN TRUE THEN text 'xxx' ELSE TRUE END;
> SELECT CASE WHEN TRUE THEN text 'xxx' ELSE '{1}'::INT[] END;
> SELECT CASE WHEN TRUE THEN text 'xxx' ELSE array [1] END;
>
> "char" is no different other than, by default, it happens to look a
> lot like any value of text type.
So much so that it has the same name as a text type (wrapped in
quotes) and behaves a lot like one:
test=# SELECT CASE WHEN TRUE THEN text 'xxx' ELSE 'a'::"char" END;
case
--
xxx
(1 row)
test=# select upper('a'::"char");
upper
---
A
(1 row)
test=# select char_length('a'::"char");
char_length
-
1
(1 row)
test=# select substring('a'::"char" from 1 for 1);
substring
---
a
(1 row)
Making it behave so much like character-based types and giving it a
name which implies that it is character based and storing a character
in it, but then not treating it like other character types in the
CASE context is bound to cause surprises for people.
> It's a different type (that happens to have some implicit casts to
> confuse things) and hence I can't see why invalid literals should
> not be thrown out.
Only, I guess, because of the name. If it weren't called "char" I
guess I wouldn't be concerned about people expecting it to behave
something like char. If "char" behaved more like char, the 'xxx'
literal wouldn't be taken as input to the type in the above CASE
statement.
-Kevin
--
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 #5028: CASE returns ELSE value always when type is "char"
Tom Lane wrote: > I'm not certain what you're trying to say, but the above is complete > nonsense ... Ah, so it is. I thought someone up-thread said that in this case it wound up as bpchar; but I see that's not so: test=# select pg_typeof((select case when true then 'xxx' else 'a'::char(1) end)); pg_typeof --- character (1 row) All that's done is to strip off the length. I guess that since the "char" type is documented as being for internal use, these issues would only affect those who choose to write queries against catalog tables or use an internal type in their tables, so I guess it's not worth going to extremes to make it behave like char. Given all that, I'll conceed the point, and give a +1 for the error message. -Kevin -- 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 #5053: domain constraints still leak
Sam Mason wrote: > the deeper problem seems to be that the table was created as: > > create table test (a tstdom); > > and not as: > > create table test (a tstdom not null); Given that tstdom is declared as NOT NULL, is this difference considered a *feature* or is it an implementation quirk? -Kevin -- 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 #5102: Silent IN (list of strings) failure to detect syntax error when list is linewrapped
"Geoff Tolley" wrote:
> postgres=# SELECT 'hello' WHERE '1' IN ('1'
> postgres(# '2');
Per the SQL standard, that is the same as
SELECT 'hello' WHERE '1' IN ('12');
I believe that's intended to make it easier to code long string
literals without creating query text which has long line lengths, but
they (understandably) don't require a minimum length for the string
fragments.
-Kevin
--
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 #5105: "Select Into Strict" does not throw NO_DATA_FOUND
"Walter Mesz" wrote: > my problem is that this select into does not throw a NO_DATA_FOUND > if my select involves a max(). I did not see this behaviour > documented anywhere and could not find it in a reasonable time at > google. > SELECT max(tanum) >INTO STRICT x >FROM lo_prod_req > WHERE tanum = '1234567'; The documentation says: $ If the STRICT option is specified, the query must return exactly one $ row or a run-time error will be reported http://www.postgresql.org/docs/8.3/interactive/plpgsql-statements.html In this case the query will always return one row. The row may have a NULL if no matching values were found, but the row will be there. select max(x) from (select generate_series(1,10) as x) y where x > 10; max - (1 row) Not a bug. -Kevin -- 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 #5102: Silent IN (list of strings) failure to detect syntax error when list is linewrapped
Tom Lane wrote: > ... Actually, I just noticed that there *is* a bug here: > > regression=# select '1' /* foo > regression*# */ > regression-# '2'; > ERROR: syntax error at or near "'2'" > LINE 3: '2'; > ^ > regression=# > > The above should be accepted, but it isn't. It works with the -- comment format. Has the C format been added to the standard, or is it an extension? If the latter, support for it would be up to the PostgreSQL community -- it's only a bug if we say it is. cc=> select 'a' --comment 'b'; ?column? -- ab (1 row) cc=> select 'a' -- comment -- comment 'b'; ?column? -- ab (1 row) -Kevin -- 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] issue with integer nullable column and value 0
Sean Hsien wrote: > using the latest JDBC driver type 4. > I have a nullable integer column in one of my tables. When I'm > updating the column in 8.4 Windows with value 0, it stays as null, > but on the Linux 8.1 it will try to update it with the value 0. Could you post a small, self-contained example of code which exhibits this problem? Also, what are the OS and Java versions on the client side? -Kevin -- 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 #5115: ADD UNIQUE table_constraint with expression
"Vladimir Kokovic" wrote:
> For ALTER TABLE ADD CONSTRAINT documentation says:
> ADD table_constraint
> This form adds a new constraint to a table using the same syntax
< as CREATE TABLE.
Which is specified as UNIQUE ( column_name [, ... ] )
> But if expression is used
it's not supported syntax, per the above.
> Create index is OK:
as one would expect from the documentation:
( { column | ( expression ) } [ opclass ] [, ...] )
This is not a bug.
Maybe there's a feature request in there, but that would belong on
a different list.
-Kevin
--
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] issue with integer nullable column and value 0
Sean Hsien wrote: > 2009/10/15 Kevin Grittner : >> what are the OS and Java versions on the client side? > I'm using CentOS 5.2 64-bits with postgres 8.1.11 + java 6u16, and > Windows Vista 32-bits with postgres 8.4.1 + java 6u13. So the Java code is running on the same machine as the database in each case? >> Could you post a small, self-contained example of code which >> exhibits this problem? > Here is a small code snippet A self-contained example would include creation and population of the table, as well as enough code to actually run the snippet and show the results. You should run this in both environments to confirm that the problem indeed manifests as you describe with the example you provide. -Kevin -- 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 #5118: start-status-insert-fatal
"Gerhard Leykam" wrote: > I am using a start script to set up my PostgreSQL database: it runs > initdb, if not done yet, starts the instance with pg_ctl start and > checks everything is fine by pg_ctl status. > > If there is another PostgreSQL database on the same machine > listening to the same port, postmaster comes up, pg_ctl status says > everthings fine, but postmaster falls down with appropriate message > in postgres.log. This is definitely not a PostgreSQL bug. Perhaps the best place to start, before suggesting a new PostgreSQL feature to solve this, would be to post to one of the other lists (admin, maybe?) and describe what you are trying to accomplish with your script, along with the problems you've found with your current version of the script. With a little more information, someone might be able to suggest a solution. (Since you're running on Linux, something involving the lockfile utility might suffice.) -Kevin -- 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 #5118: start-status-insert-fatal
Tom Lane wrote: > Well, it's arguably a start-script bug OK. > While mulling that it occurred to me that some additional output > from the postmaster would help to solve another thing that's an > acknowledged shortcoming of pg_ctl, namely that it can't parse > postgresql.conf to find out where the postmaster's communication > socket is; > cf http://archives.postgresql.org/pgsql-bugs/2009-10/msg00024.php > and other older complaints. > > We could redefine things so that it doesn't need to do that (and > also doesn't need to try to intuit the postmaster's port number, > which it does do now, but not terribly well). Suppose that after > the postmaster is fully up, it writes a file > $PGDATA/postmaster.ports, with contents along the lines of > > 5432 > /tmp/.s.PGSQL.5432 The listen_addresses setting would need to figure in, too. http://archives.postgresql.org/pgsql-hackers/2009-10/msg00022.php Matching that stuff up could start to get a little messy, but it should be doable somehow. This seems likely to overlap the review I was soon going to do of the differences between pg_ctl behavior and what is required for LSB conformance. I'll make sure to test this behavior along with others. One of my current complaints is that pg_ctl doesn't wait until it is actually ready to receive connections before returning an indication of success. I see that I neglected that point in my recently proposed LSB conforming script, but I'm guessing that this fits with other points in the argument that if what I'm doing in the script is demonstrably better than current pg_ctl behavior, we should change pg_ctl to support it rather than scripting around it. (Not that it would be hard to add ten or twenty lines to the script to cover this) -Kevin -- 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 #5118: start-status-insert-fatal
"Kevin Grittner" wrote: > I neglected that point in my recently proposed LSB conforming script Hmmm... On review, I see that I assumed that the -w switch on pg_ctl start would cover this. I see that the problem is that this uses psql to connect to the specified port. Besides the problems Tom mentioned with its heuristics to find the right port number for this cluster, there is the OP's point that connections will go to the competing cluster. One thought that occurs to me is that instead of, or in addition to, the new file Tom proposes, the "other cluster" issue could be solved by having a pg_postmaster_pid function in addition to the pg_backend_pid function. This would allow pg_ctl or a script to connect to a port and see if it is the expected postmaster process. -Kevin -- 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 #5118: start-status-insert-fatal
Tom Lane wrote: > I would rather see us implement the hypothetical pg_ping protocol > and remember to include the postmaster's PID in the response. One > of the worst misfeatures of pg_ctl is the need to be able to > authenticate itself to the postmaster, and having it rely on being > able to actually issue a SQL command would set that breakage in > stone. Sounds good to me, other than it stalls pg_ctl revamp until pg_ping is done. I don't remember a clear design of what pg_ping should look like. Does anyone have a clear plan in their head? -Kevin -- 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 #5118: start-status-insert-fatal
Tom Lane wrote: > [ thinks... ] Maybe we could have the postmaster generate a random > number at start and include that in both the postmaster.ports file > and its pg_ping responses. That would have a substantially lower > collision probability than PID, if the number generation process > were well designed; and it wouldn't risk exposing anything sensitive > in the ping response. Unless two postmasters could open the same server socket within a microsecond of one another, a timestamp value captured on opening the server socket seems even better than a random number. Well, I guess if someone subverted the clock it could mislead, but is that really more likely to cause a false match than a random number? -Kevin -- 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 #5118: start-status-insert-fatal
Tom Lane wrote: > I'm not sure whether we'd want to provide a function within libpq > for this, or just code it in pg_ctl. I'm inclined to think there would be value to a pg_ping utility to support automated monitoring by unprivileged users on other boxes. That both suggests libpq as the location, and one or two additional pieces of information. An indication of "in archive recovery" versus production or shutdown, for example, might be useful. I'm not sure what else might make sense. > Within libpq the natural thing would be to take a conninfo > connection string, but I'm not sure that suits pg_ctl's purposes. I'm a little lost on that. Would it cause any problems for pg_ctl, or just be more than it would need if it's only implemented there? -Kevin -- 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 #5118: start-status-insert-fatal
Pedro Gimeno wrote: > Tom Lane wrote: > >> This could be addressed by having the postmaster report its $PGDATA >> value in the pg_ping response, but I would be against that on >> security grounds. We don't let nonprivileged users know where >> PGDATA is, why would we make the information available without any >> authentication at all? > > Maybe a hash of it? I'm not really clear on why it's a security issue for someone to know the $PGDATA value, but if it is, there are some "typical" locations for which a hash could be generated and matched against the returned hash; so a hash of it would only be safe for those who chose sufficiently "creative" directory paths. On top of that, I'm not sure it's a very useful way to confirm that you've connected to the correct instance. We often get requests to replace the contents of a development or test database with a dump from a production database. More than once, the DBA doing this has forgotten to stop PostgreSQL before deleting the $PGDATA directory and creating it fresh for the restore of the PITR dump. When we attempt to start the new copy, which has the same $PGDATA, owner, and port number as the copy still running in the deleted directory, we have similar issues to those described in the original post. So, personally, I consider the data directory a less reliable test than the pid. (We don't have a lot of OS crash & reboot occurrences.) -Kevin -- 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 #5118: start-status-insert-fatal
Robert Haas wrote: > Well, then Tom's idea of using a random number seems pretty solid no > matter how you slice it. Maybe a UUID. A random number is looking like the best option. I'm not sure why I'd want to generate a perfectly good 128 bit random number and then throw away six of the bits to dress it up as a UUID, though. Do the libraries for that do enough to introduce entropy to compensate for the lost bits? Any other benefit I'm missing? -Kevin -- 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 #5118: start-status-insert-fatal
Tom Lane wrote: > I was envisioning just using PostmasterRandom() (after initializing > the seed from time(NULL) as we do now). I don't think we need a > super-wide random number. Fine with me. Just that and CanAcceptConnections in the response? It seems like pg_ping (client utility and related postmaster support) should be a discrete patch. Improvements to pg_ctl and init scripts would come later, as separate patches? -Kevin -- 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 #5118: start-status-insert-fatal
Tom Lane wrote: > Alternatively, do the postmaster support and make the > presumably-minor pg_ctl mods to use it, and then a standalone > pg_ping utility could come later. I'm not sure how big the utility > would be, but surely bigger than the delta in pg_ctl. Bigger than the delta for *just this change* to pg_ctl. I was thinking of addressing all pg_ctl issues at once, but perhaps this one makes sense on its own. If so, your alternative does sound better. -Kevin -- 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 #5118: start-status-insert-fatal
Robert Haas wrote: > UUIDs throw away 6 bits? http://en.wikipedia.org/wiki/Universally_Unique_Identifier#Version_4_.28random.29 -Kevin -- 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 #5127: AbstractJdbc2Connection#doRollback should throws Exception if connection is closed
wrote: > If PostgreSQL server is restarted, old Connection pooled in > Application server's ConnectionPool cannot connect to DB. > That's OK. > But, I can call rollback() on old Connection and it throws no > exception. Hmmm What problem are you having? The transaction would have been rolled back when the server was restarted (or if the connection was broken). What benefit would you get from the exception you suggest? -Kevin -- 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 #5127: AbstractJdbc2Connection#doRollback should throws Exception if connection is closed
takiguchi wrote:
> This is a problem of connection pooling, not of transaction.
>
> public void testConnection() {
>Connection con = dataSource.getConnection(); // get a connection
> from pool (If DB server restarted, invalid connection will be
> returned)
>boolean valid = true;
>try {
>// execute some DMLs...
>con.commit();
>} catch (SQLException e) {
>try {
>con.rollback();
>} catch (SQLException e) {
>valid = false; // UNREACHABLE
>}
>} finally {
>if (valid) {
>con.close(); // Connection#close() doesn't close
> connection in reality in connection pooling mechanism. It simply
> returns the connection to pool.
>}
>}
> }
>
> Because rollback() throws no exception when physical connection has
> been closed, I cannot know whether rollback was successfully
> completed.
> In general, if rollback() throws NO exception, it must be a success.
> (Success means rollback process is executed, and ended successfully.
> This represents that connection could access to server.)
>
> I think if physical connection has closed, Connection's method
> should fail and throws Exception.
That's sort of an odd construct, but I see your point. Checking the
javadocs, I see that they very explicitly support your position.
http://java.sun.com/javase/6/docs/api/java/sql/Connection.html#rollback%28%29
| SQLException - if a database access error occurs, this method is
| called while participating in a distributed transaction, this method
| is called on a closed connection or this Connection object is in
| auto-commit mode
This is indeed a bug. No doubt about it.
Since it is a JDBC driver bug, it might be best to post to that list,
with a reference back to this thread. Do you want to put together a
JDBC driver patch, or should I?
-Kevin
--
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 #5127: AbstractJdbc2Connection#doRollback should throws Exception if connection is closed
takiguchi wrote:
> public void testConnection() {
>Connection con = dataSource.getConnection(); // get a connection
> from pool (If DB server restarted, invalid connection will be
> returned)
>boolean valid = true;
>try {
>// execute some DMLs...
>con.commit();
>} catch (SQLException e) {
>try {
>con.rollback();
>} catch (SQLException e) {
>valid = false; // UNREACHABLE
>}
>} finally {
>if (valid) {
>con.close(); // Connection#close() doesn't close
> connection in reality in connection pooling mechanism. It simply
> returns the connection to pool.
>}
>}
> }
I'm looking at the JDBC driver, and so far I can't see why a rollback
attempt wouldn't generate a SQLException when the commit attempt did
so for a broken connection.
Is it possible that you have autoCommit set to true? The driver is
currently skipping the commit or rollback attempts when that is true,
which is improper; but I'm not sure you're going to be very happy with
the above code if we make it behave like the Sun javadocs require,
either. With autoCommit set to true, *any* commit or rollback attempt
should throw an exception, so in that case the above code would never
return a connection to the pool, nor would it close the connection
properly.
This makes me concerned that fixing the bug in the JDBC driver could
expose serious bugs in application code, and break things which are
currently working, for some values of "working". :-(
-Kevin
--
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 #5212: incorrect resource manager data checksum in record at ...
"Amaya Gamarra" wrote: > PostgreSQL version: 8.1.11 > We've got a Slony-I cluster over 2 postgres 8.1.11 servers. > I join the pgsql.conf file. > logging_collector = on That option (and others) are not present in 8.1. Either that's not your version or it's not your postgresql.conf file. -Kevin -- 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 #5225: create table: cast necessary for constant??
Craig Ringer wrote: > On 3/12/2009 12:35 AM, Tom Lane wrote: >> You really ought to cast the 'I' to some specific type. > > It's usually neatest to do this by just explicitly identifying > the intended type in the first place, eg: > > > SELECT firmnr, > werknr, > TEXT 'I' as invper, > invnum > from ; > > ... which, IIRC, is the standard way to do it. I don't have a copy > to check against to be sure. > > Personally, I like the fact that Pg errs on the side of caution > here rather than guessing what you want. We should probably have some wiki page or something to which we can refer people when they raise this, which is bound to happen from time to time, since the PostgreSQL behavior is a deviation from the standard. Now, I've been persuaded that there are good reasons for the deviation, and that workarounds for code previously written to standard are relatively straightforward, but many people here lose sight of the fact that it *is* a deviation when replying to someone who's just run into it. Quoting from section 5.3 of "WG3:HBA-003 H2-2003-305 August, 2003 (ISO-ANSI Working Draft) Foundation (SQL/Foundation)": | 13) The declared type of a is | fixed-length character string. The length of a is the number of s | that it contains. Each contained in represents a single in both the value | and the length of the . The two | s contained in a shall not be separated | by any . | | NOTE 72 * s are allowed to be | zero-length strings (i.e., to contain no characters) even | though it is not permitted to declare a that is | CHARACTER with 0 (zero). Treating an otherwise unadorned set of characters between two apostrophes as anything except a character string literal of type CHARACTER with a length calculated per the above violates the standard. Rather than pretending otherwise, we should be prepared to explain the reasons for the deviation, describe what the PostgreSQL behavior *is*, and justify the deviation. -Kevin -- 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 #5225: create table: cast necessary for constant??
Tom Lane wrote: > Sorry about that --- I had confused this case with that of a bare > NULL literal, which Postgres treats the same as an unadorned > string literal for type determination purposes. You're right that > the spec treats them differently. This is feasible for the spec's > purposes because it has such a paucity of data types. Yeah, the arguments about how the PostgreSQL behavior makes it easier to work with user defined types are compelling. The differences in behavior don't show up often -- I suspect that they will typically be encountered by those converting from other products to PostgreSQL. I'm just suggesting that someone put together a page in the wiki or documentation to describe the differences in behavior and the normal workarounds. That might preempt some of the problems, and would be a quick way to help someone who runs into the issue for the first time. > Also, I believe that the spec expects you to explicitly mark > literals that aren't to be treated as plain strings, ie, in > something like > TIMESTAMP '2009-12-02 18:28:58' > you're not really supposed to omit the word TIMESTAMP. Absolutely true. Although many products will tolerate omission for date/time literals, that's non-standard behavior. The reason they do that is pretty much the same reason that PostgreSQL does, but PostgreSQL takes it farther. > Postgres has a whole lot of datatypes, including user-added ones, > and most of them share the unadorned string literal as the base > case for constants. Giving preference to CHARACTER would make > that machinery a lot less pleasant to use. Well put. -Kevin -- 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 #5225: create table: cast necessary for constant??
"Wagner, Kurt" wrote: > when writing a character constant elsewhere > then at first it is interpreted as character constant - right? > then it is casted to the desired type No. It was confusing for me, too; but the PostgreSQL behavior is to treat what the standard calls a as being of type UNKNOWN -- just like the behavior described in the spec for NULL. When it is used in some context where the type must be resolved, it then tries to pick an appropriate type. (I believe there is some slight preference for type TEXT when there are multiple possibilities.) This is helpful for those wanting to use literals of non-standard types. (Many people use PostgreSQL specifically because of the ability to define custom types and operators.) There is an understandable tendency of those who work deep in the guts of the PostgreSQL software, making all this custom type code work, that those coming into PostgreSQL from other environments come with the assumption that these literals will be treated as character strings. From their perspective there is nothing more natural than to view such a literal as lacking any type information, with the obvious implication that you should explicitly give it a type. Once you shake out any problems from code you are migrating, you'll find it's not hard to write new code in a way which will work in either environment. -Kevin -- 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 #5225: create table: cast necessary for constant??
"Kevin Grittner" wrote: > There is an understandable tendency of those who work deep in the > guts of the PostgreSQL software, making all this custom type code > work, I mangled that sentence worse than usual. The tendency is to see the PostgreSQL behavior as natural and to forget the expectations of those coming in. I shouldn't post until the caffeine is fully in effect. -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
