Re: [HACKERS] Open 7.4 features
On Wed, 19 Mar 2003, Bruce Momjian wrote: Here are a list of features that might be in 7.4. I know there are several people involved in each of these items. Tablespaces I haven't seen any proposal for tablespaces recently. Is this something that is actively being worked on? Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] actual cvs: compile error
gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/interfaces/libpq -I../../../src/include -c -o common.o common.c -MMD common.c:184: conflicting types for `cancelConn' common.h:30: previous declaration of `cancelConn' make[3]: *** [common.o] Error 1 make[3]: Leaving directory `/var/home/PG_DEVEL/pgsql/src/bin/psql' make[2]: *** [all] Error 2 The cancelConn has defined to the file common.c as static and in the common.h as extern. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Current CVS compile problem
[Sorry for the delay, but the mail didn't reach me prior this morning.] On Mon, Mar 17, 2003 at 09:09:22AM -0500, Tom Lane wrote: Teodor Sigaev [EMAIL PROTECTED] writes: gmake[4]: *** No rule to make target `../lib/typename.o', needed by `ecpg'. Stop. Yeah, me too. I think the correct fix is '../lib' should become '../ecpglib' in ecpg/preproc/Makefile, but am waiting on Michael to confirm. You're right of course Tom. My first checkin was incomplete. I missed some files when copying over to my cvs tree. Michael -- Michael Meskes Email: [EMAIL PROTECTED] ICQ: 179140304 Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Faster NUMERIC implementation
On Wed, Mar 19, 2003 at 10:51:32PM -0500, Tom Lane wrote: I've been amusing myself the last several evenings by working on a reimplementation of the NUMERIC datatype, along the lines of previous discussion (use base-1 digits instead of base-10 so that the number of iterations of the inner loops decreases by a factor of about 4). ... Tom, I do like it since I really believe it will be faster. But I wonder if we could arrange things so the Numeric stuff wents out of the backend. As you surely noticed I created a pgtypes lib to make our special types available to the outside world. So far Numeric is the only one, but we are working on date (partly finished) and timestamp. I think it shouldn't be too difficult to make the backend call the functions inside the dynamic library instead of keeping it inside. That way we would have to code these functions only once and no double work is required. Of course the lib should then move out of interfaces, but ecpg could still call it. Any comments? Michael -- Michael Meskes Email: [EMAIL PROTECTED] ICQ: 179140304 Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Varchar Vs. Text index matching - why different?
I just noticed that comparing a text to a varchar results in an index not
being used without a cast in some circumstances. Given that they have
identical internal representations (or so I believe), that seems a little odd.
Can anyone shed some light on this for me? I assume it has something to do
with believing text-varchar is a down-cast due to the size limitation on
varchar, but don't we collect size stats of text fields (stawidth)?
The following illustrates the behaviour:
create table tt(id serial, tf text);
create unique index tfi on tt(tf);
create unique index ttpk on tt(id);
insert into tt(tf) values('fred' || currval('tt_id_seq'));
insert into tt(tf) select 'fred' || currval('tt_id_seq') from tt;
create table vt(id serial, vf varchar(31));
create unique index vfi on vt(vf);
create unique index vtpk on vt(id);
insert into vt(vf) select 'fred' || currval('vt_id_seq') from tt;
analyze;
explain select * from tt,vt where tt.id=12345 and vt.vf = tt.tf;
QUERY PLAN
-
Nested Loop (cost=1.00..10944.29 rows=1 width=32)
Join Filter: ((inner.vf)::text = outer.tf)
- Index Scan using ttpk on tt (cost=0.00..3.01 rows=1 width=16)
Index Cond: (id = 12345)
- Seq Scan on vt (cost=1.00..10531.68 rows=32768 width=16)
(5 rows)
rt3=# explain select * from tt,vt where tt.id=12345 and vt.vf = cast(tt.tf
as varchar);
QUERY PLAN
--
Nested Loop (cost=0.00..7.92 rows=1 width=32)
- Index Scan using ttpk on tt (cost=0.00..3.01 rows=1 width=16)
Index Cond: (id = 12345)
- Index Scan using vfi on vt (cost=0.00..4.89 rows=1 width=16)
Index Cond: (vt.vf = (outer.tf)::character varying)
(5 rows)
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3172 | ___ |
Http://www.rhyme.com.au |/ \|
|----
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Faster NUMERIC implementation
Michael Meskes [EMAIL PROTECTED] writes: But I wonder if we could arrange things so the Numeric stuff wents out of the backend. With suitable #define hacking you could perhaps take care of the code's dependencies on palloc/pfree ... but elog is harder, and I don't see any realistic way to handle the backend's function-call conventions as opposed to conventions that would make sense as a library API. I don't want to clutter the code by having to support two sets of error conventions and two APIs. If you can figure a way around that, great... regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Varchar Vs. Text index matching - why different?
Philip Warner [EMAIL PROTECTED] writes: I just noticed that comparing a text to a varchar results in an index not being used without a cast in some circumstances. Given that they have identical internal representations (or so I believe), that seems a little odd. But they're not the same datatype. I have been wondering whether we couldn't eliminate the datatype distinction between text and varchar. Haven't gotten around to thinking about it though; the mess in the numeric-datatype world seems higher priority. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Faster NUMERIC implementation
On Thu, Mar 20, 2003 at 09:49:30AM -0500, Tom Lane wrote: With suitable #define hacking you could perhaps take care of the code's dependencies on palloc/pfree ... but elog is harder, and I don't see any realistic way to handle the backend's function-call conventions as opposed to conventions that would make sense as a library API. I don't want to clutter the code by having to support two sets of error conventions and two APIs. If you can figure a way around that, great... How about some wrapper frunctions in the backend that just call their helper functions in the lib? Let's be honest maintaining all this code twice will be very hard to do too. I'd prefer looking for a way to integrate things. I have no problem with special backend syntax for some functions. It's not that the API has to be identical. We could have an open API and a backend API calling the same functions. Michael -- Michael Meskes Email: [EMAIL PROTECTED] ICQ: 179140304 Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] timestamp/date in ecpg
I started working on date/timestamp in ecpg. So far I can read date types from the DB and I can insert date into the DB. However there seems to be a bug in converting timestamp to ascii or vice versa. If anyone of you knows more about timestamp2tm etc. could you please have a look at function PGTYPEStimestamp_ttoa in pgtypeslib/timestamp.c? Hmm, maybe the transformation in the other direction is the culprit. What I do is call ts1 = PGTYPEStimestamp_atot(2000-7-12 17:34:29, NULL); followed by a text = PGTYPEStimestamp_ttoa(ts1); Needless to say the resulting text is not 2000-7-12 17:34:29. :-( Maybe some of you have an idea. Michael -- Michael Meskes Email: [EMAIL PROTECTED] ICQ: 179140304 Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Faster NUMERIC implementation
Michael Meskes [EMAIL PROTECTED] writes:
How about some wrapper frunctions in the backend that just call their
helper functions in the lib?
I'm not willing to do that for any very large number of functions; the
code clutter and runtime overhead would become significant.
I had some visions, back when we were first doing the v1-call-convention
stuff, that it might be possible to make a script that automatically
interprets
Datum
numeric_add(PG_FUNCTION_ARGS)
{
Numeric num1 = PG_GETARG_NUMERIC(0);
Numeric num2 = PG_GETARG_NUMERIC(1);
...
PG_RETURN_NUMERIC(res);
}
and generates a derived version like
Numeric
numeric_add(Numeric num1, Numeric num2)
{
...
return res;
}
We'd probably have to tighten the consistency of formatting a little
to make that workable, but it seems more attractive than manually
maintaining either two sets of code or a wrapper layer.
But before you get too excited about that, there's also the
error-handling issue --- and I'm definitely not interested in changing
all the subroutines away from elog to funny-return-value conventions.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Faster NUMERIC implementation
[ very off topic ] Michael Meskes [EMAIL PROTECTED] writes: I already have to manually sync code (preproc.y = gram.y) and don't like the idea of having to do it with a lot more code. I've been wondering for quite awhile if we couldn't find a way to avoid manually duplicating the backend grammar in preproc.y. Seems like a script could handle 99% of the conversion, generating the very stylized actions you need. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] A bad behavior under autocommit off mode
On Thu, 2003-03-20 at 13:41, Barry Lind wrote: However the proposed FE/BE protocol change to tell the client the transaction state would allow the driver to detect this. snip Of these two limitations the first is more significant since users do issue 'commit' statements directly sometimes, whereas users would likely never change the GUC parameter in their SQL. I like the simplicity of the GUC parameter and that is the reason I converted the jdbc driver in 7.3 to use this new method. While the first may seem more significant, it sounds like it is actually going away with the fe/be protocol changes. If thats true, it seems to me this makes the GUC method the most limiting. Robert Treat ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] A bad behavior under autocommit off mode
Tom Lane wrote: (B (B Bruce Momjian [EMAIL PROTECTED] writes: (B I think our SET functionality is easy to understand and use. I don't (B see pushing it into the client as greatly improving things, and could (B make things worse. If we can't get it right in the backend, how many (B clients are going to do it wrong? (B (B This argument overlooks the fact that most of the client libraries (B already have notions of autocommit on/off semantics that they need to (B adhere to. libpq is too simple to have heard of the concept, but I (B believe that JDBC, ODBC, and DBI/DBD all need to deal with it anyway. (B I doubt that managing a server-side facility makes their lives any (B easier ... especially not if its semantics don't quite match what (B they need to do, which seems very possible. (B (B But it'd be interesting to hear what the JDBC and ODBC maintainers (B think about it. (B (BThe current ODBC driver doesn't work well under autocommit (Boff mode at server side. However, it's not on my (at least (BASAP) TODO item. (B (Bregards, (BHiroshi Inoue (Bhttp://www.geocities.jp/inocchichichi/psqlodbc/ (B (B---(end of broadcast)--- (BTIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Varchar Vs. Text index matching - why different?
At 10:18 AM 20/03/2003 -0500, Tom Lane wrote: I have been wondering whether we couldn't eliminate the datatype distinction between text and varchar. That would be great if done magically in the backend. If all text columns were effectively wrapped in a Cast(X to text), life from a users PoV would be a little simpler. I assume Varchar(20) would still exist and be limited to 20 chars. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] probs with postgres
I keep getting this: 2003-03-20 08:15:49 WARNING: Rel users_sessions: Uninitialized page 3195 - fixing 2003-03-20 08:15:49 WARNING: Rel users_sessions: Uninitialized page 3196 - fixing 2003-03-20 08:15:49 WARNING: Rel users_sessions: Uninitialized page 3197 - fixing 2003-03-20 08:15:49 WARNING: Rel users_sessions: Uninitialized page 3198 - fixing 2003-03-20 08:15:49 WARNING: Rel users_sessions: Uninitialized page 3199 - fixing 2003-03-20 08:15:49 WARNING: Rel users_sessions: Uninitialized page 3200 - fixing What could be causing it? It occurs during Vacuum I think. We have been having a lot of load problems with Postgres since we launched our new site a day ago and now this problem has cropped up. users_sessions is probably our most-written row. Postgres 7.3.2. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] ALTER TABLE / CLUSTER ON
Does the new ALTER TABLE / CLUSTER ON syntax support the ONLY modifier - it isn't documented if it is?? I guess it's not really relevant is it? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ALTER TABLE / CLUSTER ON
I just managed to break the CLUSTER ON patch: test=# create table test (a int4 primary key, b int4 unique, c int4); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'test_pkey' for table 'test' NOTICE: CREATE TABLE / UNIQUE will create implicit index 'test_b_key' for table 'test' NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'test_pkey' for table 'test' NOTICE: CREATE TABLE / UNIQUE will create implicit index 'test_b_key' for table 'test' CREATE TABLE test=# create index blah on test(c); CREATE INDEX test=# alter table test cluster on a; ERROR: ALTER TABLE: cannot find index a for table test ERROR: ALTER TABLE: cannot find index a for table test test=# alter table test cluster on blah; ALTER TABLE test=# alter table only test cluster on blah; ERROR: parser: parse error at or near cluster at character 23 ERROR: parser: parse error at or near cluster at character 23 test=# alter table test cluster on blah; NOTICE: ALTER TABLE: table test is already being clustered on index blah WARNING: Cache reference leak: cache pg_index (16), tuple 0 has count 1 NOTICE: ALTER TABLE: table test is already being clustered on index blah WARNING: Cache reference leak: cache pg_index (16), tuple 0 has count 1 ALTER TABLE Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Roadmap for FE/BE protocol redesign
On Monday 10 March 2003 10:51 am, Tom Lane wrote: * XML support? If we do anything, I'd want some extensible solution to allowing multiple query-result output formats from the backend, not an XML-specific hack. For one thing, that would allow the actual appearance of any XML support to happen later. It seems this would also be a good solution to a previous discussion about boolean representation. The postgres output of t/f is perfectly resonable, but can be somewhat confusing to someone that relies on PHP's typecasting. In the discussion, someone mentioned that if you take in a variable directly from the database and cast it to boolean, both 't' and 'f' will cast to true. It turned out to be even more confusing because MySQL uses 0/1 which cast properly. If I remember correctly, there was even talk of adding a run-time parameter similar to the datestyle. If it were all handled in the query-result output formatting functions like you suggest, that would seem like a much cleaner solution. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] date index problems
This behaviour I find unusual: usa=# explain analyze select user_id, plan_next from users_profiles where plan_next = '2003-01-01'; QUERY PLAN --- Index Scan using users_profiles_plan_next_key on users_profiles (cost=0.00..614.01 rows=228 width=8) (actual time=0.41..0.41 rows=0 loops=1) Index Cond: (plan_next = '2003-01-01'::date) Total runtime: 0.49 msec (3 rows) usa=# explain analyze select user_id, plan_next from users_profiles where plan_next = '2003-01-01'::date - interval '1 week'; QUERY PLAN --- Seq Scan on users_profiles (cost=0.00..1076.08 rows=184 width=8) (actual time=109.48..109.48 rows=0 loops=1) Filter: ((plan_next)::timestamp without time zone = '2002-12-25 00:00:00'::timestamp without time zone) Total runtime: 109.56 msec (3 rows) Surely the planner is aware that '2003-01-01'::date - interval '1 week' is a constant??? Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] date index problems
Surely the planner is aware that '2003-01-01'::date - interval '1 week' is a constant??? Actually, turns out that the planner was smarter than me I think. 2003-01-01 occurs very rarely in the system, but other dates occupy 1/7th of the table, so it's not so easy to plan... Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] date index problems
On Fri, 21 Mar 2003, Christopher Kings-Lynne wrote: This behaviour I find unusual: usa=# explain analyze select user_id, plan_next from users_profiles where plan_next = '2003-01-01'; QUERY PLAN --- Index Scan using users_profiles_plan_next_key on users_profiles (cost=0.00..614.01 rows=228 width=8) (actual time=0.41..0.41 rows=0 loops=1) Index Cond: (plan_next = '2003-01-01'::date) Total runtime: 0.49 msec (3 rows) usa=# explain analyze select user_id, plan_next from users_profiles where plan_next = '2003-01-01'::date - interval '1 week'; QUERY PLAN --- Seq Scan on users_profiles (cost=0.00..1076.08 rows=184 width=8) (actual time=109.48..109.48 rows=0 loops=1) Filter: ((plan_next)::timestamp without time zone = '2002-12-25 00:00:00'::timestamp without time zone) Total runtime: 109.56 msec (3 rows) I presume the type of plan_next is 'date'. Does casting '2003-01-01'::date - interval '1 week' to date help? Gavin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] probs with postgres
Christopher Kings-Lynne [EMAIL PROTECTED] writes: I keep getting this: 2003-03-20 08:15:49 WARNING: Rel users_sessions: Uninitialized page 3195 - fixing Hmm. In 7.2.* I'd have said this was a known problem, but in 7.3.* it's not. Want to dig into it? This is what I know about the 7.2 problem: http://fts.postgresql.org/db/mw/msg.html?mid=1357214 regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] date index problems
Gavin Sherry [EMAIL PROTECTED] writes: I presume the type of plan_next is 'date'. Does casting '2003-01-01'::date - interval '1 week' to date help? Easier would be '2003-01-01'::date - 7 which yields a date to start with. But yeah, date minus interval yields a timestamp, which will not automatically downconvert to a date, thus you don't get to use an index on date. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] probs with postgres
Christopher Kings-Lynne [EMAIL PROTECTED] writes: I keep getting this: 2003-03-20 08:15:49 WARNING: Rel users_sessions: Uninitialized page 3195 - fixing Hmm. In 7.2.* I'd have said this was a known problem, but in 7.3.* it's not. Want to dig into it? This is what I know about the 7.2 problem: http://fts.postgresql.org/db/mw/msg.html?mid=1357214 OK, well I can tell you this at the moment: 1. We're running 7.3.2 2. We do an hourly VACUUM ANALYZE. I do the odd VACUUM FULL (not on that table) when I do large changes to tables. 3. users_sessions has heaps and heaps of reads and updates regular scan deletes. There are lots of SELECT..FOR UPDATEs done on the table 4. Table def: usa=# \d users_sessions Table public.users_sessions Column | Type | Modifiers -+--+ sid | character varying(32)| not null name| character varying(32)| not null val | text | changed | timestamp with time zone | not null default 'now' uid | integer | not null Indexes: users_sessions_pkey primary key btree (sid, name), users_sessions_cha_name_idx btree (changed, name), users_sessions_uid_idx btree (uid) 5. We have been having load and stability problems with our Postgres since we released a massive upgrade to our website. We did actually have one time when Postgres crashed and the whole server rebooted when we got a bunch of these: Mar 20 09:25:54 serendipity /kernel: pmap_collect: collecting pv entries -- suggest increasing PMAP_SHPGPERPROC Mar 20 09:27:09 serendipity /kernel: pmap_collect: collecting pv entries -- suggest increasing PMAP_SHPGPERPROC Mar 20 09:28:29 serendipity last message repeated 2 times Mar 20 09:29:42 serendipity /kernel: pmap_collect: collecting pv entries -- suggest increasing PMAP_SHPGPERPROC So I reduced our shared buffers by a few thousand and I'm waiting on our sysadmin to up the max pages per proc in the kernel. Any ideas on anything I can look into? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] probs with postgres
Christopher Kings-Lynne [EMAIL PROTECTED] writes: 5. We have been having load and stability problems with our Postgres since we released a massive upgrade to our website. We did actually have one time when Postgres crashed and the whole server rebooted when we got a bunch of these: Mar 20 09:25:54 serendipity /kernel: pmap_collect: collecting pv entries -- suggest increasing PMAP_SHPGPERPROC [blink...] Perhaps you should be asking hard questions about the stability of your kernel. What are you running, anyway? This is not to say that Postgres might not have its own problems --- but Postgres is an unprivileged process. If your kernel is crashing, it is not Postgres that's broken. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Fwd: Re: [HACKERS] [GENERAL] Extracting time from timestamp
On Friday 21 Mar 2003 11:38 am, Christopher Kings-Lynne wrote: phd=# select time(abstime(timestamp 'now')) from bookings; ERROR: parser: parse error at or near abstime at character 13 phd=# select time(timestamp 'now') from bookings; ERROR: parser: parse error at or near timestamp at character 13 phd=# select version(); version Try: select time(abstime(timestamp 'now')) from bookings; select time(timestamp 'now') from bookings; First of all, thanks, it worked.. And What's so holy about if it is a function? That was bummer, I admit.. Spent almost a day on it.. Shridhar P.S. Sorry, I didn't realized that I handn' CC hackers.. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [GENERAL] Extracting time from timestamp
select time(abstime(timestamp 'now')) from bookings; select time(timestamp 'now') from bookings; First of all, thanks, it worked.. And What's so holy about if it is a function? It's really old 7.1 syntax, not supported from 7.2+. Basically it's because time can now have a precision. eg. a field of type TIME(4) will have decimal places of millisecond precision. You need to quote the function to make it get treated as a function rather than a type definition... A better (standard) way to express it is probably: select cast(cast(current_timestamp as abstime) as time) from bookings; or even select current_timestamp::abstime::time from bookings; Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: Fwd: Re: [HACKERS] [GENERAL] Extracting time from timestamp
Shridhar Daithankar[EMAIL PROTECTED] [EMAIL PROTECTED] writes: And What's so holy about if it is a function? The problem is that TIME(n) is a datatype name, not a function call, according to the SQL spec. Likewise for TIMESTAMP(n), INTERVAL(n), NUMERIC(m,n), and maybe one or two other special cases I've forgotten. The SQL spec's love of special-purpose syntaxes is one of its worst features IMHO ... regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Fwd: Re: [HACKERS] [GENERAL] Extracting time from timestamp
On Friday 21 Mar 2003 12:25 pm, Tom Lane wrote: Shridhar Daithankar[EMAIL PROTECTED] [EMAIL PROTECTED] writes: And What's so holy about if it is a function? The problem is that TIME(n) is a datatype name, not a function call, according to the SQL spec. Likewise for TIMESTAMP(n), INTERVAL(n), NUMERIC(m,n), and maybe one or two other special cases I've forgotten. The SQL spec's love of special-purpose syntaxes is one of its worst features IMHO ... In this case, I would vote for overload as SQL extension in postgresql if people feel it is feasible and/or sensible.. Shridhar ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested transactions: low level stuff
On Thu, Mar 20, 2003 at 01:40:44AM -0500, Tom Lane wrote:
There are indeed some first-cut provisions in the lock code for multiple
transactions owned by a backend, but it'd be dangerous to assume that
they are either correct or complete. The only case that's tested is for
VACUUM to hold a lock across two transactions --- and this lock will not
be held in the face of an error, so it's not an accurate representation
of nested xacts anyway.
Well, the only way to see if they are right or wrong is testing them. I
will be trying to completely understand the transaction/block states so
I can implement the needed state machinery for nested transaction; with
this we can play with locks and the rest of resources.
I think this transaction state game is the easiest part of nested
transactions.
Also see LW locks, which have no such management infrastructure ...
You can't end a transaction holding one of those; failure to do so is a
programming error. The only way it is allowed is when elog(ERROR) is
called. For that I propose that held_lwlocks is replaced with
typedef struct held_lwlocks
{
TransactionId xid[MAX_SIMUL_LWLOCKS];
LWLockIdlid[MAX_SIMUL_LWLOCKS];
int num_locks_held;
} held_lwlocks;
and LWReleaseAll() modified appropiately.
--
Alvaro Herrera (alvherre[a]dcc.uchile.cl)
Nunca confiaré en un traidor. Ni siquiera si el traidor lo he creado yo
(Barón Vladimir Harkonnen)
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] A bad behavior under autocommit off mode
Tom, From the jdbc driver perspective I prefer the GUC variable approach, but either can be used. Each has limitations. In 7.2 and earlier jdbc code the driver handled the transaction symantics by adding begin/commit/rollback in appropriate places. And that code is still in the 7.3 driver to support older servers. In 7.3 the driver uses the GUC variable to control the transaction state. In general this is easier since it is a set once and forget about it operation. As I mentioned earlier each method has limitations. Let me list them. The problem with managing the state on the client is that in order for this to work the client needs to intercept all transaction ending events in order to start the next transaction when running in non-autocommit mode. Thus each 'commit' becomes 'commit; begin;'. Since the jdbc API has a commit() and rollback() method there is an obvious place to insert this logic. However if the user directly issues a commit or rollback sql call (instead of using the jdbc api) then the driver isn't in a position to start the new transaction, unless the driver starts parsing all SQL looking for commits or rollbacks which I am reluctant to do. However the proposed FE/BE protocol change to tell the client the transaction state would allow the driver to detect this. The problem with using the GUC approach is that if the user in SQL changed the GUC value the driver would have no way to know the state change. And thus the driver would think it was opperating in one mode (the mode *it* set), but actually be running in a different mode (the mode the *user* set through SQL). Of these two limitations the first is more significant since users do issue 'commit' statements directly sometimes, whereas users would likely never change the GUC parameter in their SQL. I like the simplicity of the GUC parameter and that is the reason I converted the jdbc driver in 7.3 to use this new method. thanks, --Barry Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I think our SET functionality is easy to understand and use. I don't see pushing it into the client as greatly improving things, and could make things worse. If we can't get it right in the backend, how many clients are going to do it wrong? This argument overlooks the fact that most of the client libraries already have notions of autocommit on/off semantics that they need to adhere to. libpq is too simple to have heard of the concept, but I believe that JDBC, ODBC, and DBI/DBD all need to deal with it anyway. I doubt that managing a server-side facility makes their lives any easier ... especially not if its semantics don't quite match what they need to do, which seems very possible. But it'd be interesting to hear what the JDBC and ODBC maintainers think about it. Perhaps autocommit as a GUC variable is just what they want. Please recall that GUC-autocommit in its current form was my idea, and I rushed it in there because I wanted us to be able to run the NIST compliance tests easily. In hindsight I am thinking it was a bad move. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] string || NULL ambiguity
On Wed, 2003-03-19 at 20:32, Oleg Bartunov wrote:
On Wed, 19 Mar 2003, Alvaro Herrera wrote:
stringA||COALESCE(NULL, '')
we don't know in advance if it's NULL or not.
Right, that's the point of COALESCE: the first non-NULL argument is
returned -- so if the first argument to COALESCE happens to be non-NULL,
COALESCE has no effect.
I tried this with a txtidx column type:
tsearch=# select coalesce(NULL,'');
case
--
(1 row)
tsearch=# select coalesce(NULL,'hi');
case
--
hi
(1 row)
tsearch=# select title_fts from article;
title_fts
--
'2':3A 'tsearch':1A 'version':2A
'easi':1A 'implement':2A
(3 rows)
tsearch=# select coalesce('',title_fts) from article;
ERROR: Void value
tsearch=# select coalesce('hi',title_fts) from article;
case
--
'hi'
'hi'
'hi'
(3 rows)
---
Note the error: ERROR: Void value above. Why is that happening?
--
Thomas
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] string || NULL ambiguity
On Wed, 2003-03-19 at 20:32, Oleg Bartunov wrote:
On Wed, 19 Mar 2003, Alvaro Herrera wrote:
stringA||COALESCE(NULL, '')
we don't know in advance if it's NULL or not.
Right, that's the point of COALESCE: the first non-NULL argument is
returned -- so if the first argument to COALESCE happens to be
non-NULL, COALESCE has no effect.
I tried this with a txtidx column type:
tsearch=# select coalesce(NULL,'');
case
--
(1 row)
tsearch=# select coalesce(NULL,'hi');
case
--
hi
(1 row)
tsearch=# select title_fts from article;
title_fts
--
'2':3A 'tsearch':1A 'version':2A
'easi':1A 'implement':2A
(3 rows)
tsearch=# select coalesce('',title_fts) from article;
ERROR: Void value
tsearch=# select coalesce('hi',title_fts) from article;
case
--
'hi'
'hi'
'hi'
(3 rows)
---
Sorry the above should have been:
tsearch=# select coalesce(title_fts, '') from article;
ERROR: Void value
tsearch=# select coalesce(title_fts, 'hi') from article;
case
--
'2':3A 'tsearch':1A 'version':2A
'easi':1A 'implement':2A
'hi'
(3 rows)
Note the error: ERROR: Void value above. Why is that happening?
--
Thomas
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [INTERFACES] Roadmap for FE/BE protocol redesign
Bruce Momjian writes: True, but GUC seems like the way to go, and we have per-user/db settings for GUC. But the required autocommit setting depends neither on the user nor the database, it depends on the identity of the client application. That type of granularity is not offered by GUC. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] More outdated examples
The specific case that Tom complained about wasn't even in the latest documentation sources anymore. I think the patch should be reverted. Bruce Momjian writes: The following applied patch removes the section Tom thought wasn't needed anymore. --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I can find no suitable replacement for this example. Can anyone else? Peter Eisentraut wrote: This example in User's Guide section 7.2 doesn't work anymore at all: tgl= select @ text '-4.5' as abs; Both of the examples in this section have been obsoleted by our recent elimination of a lot of implicit casts. This one would still work if text-to-float8 were an implicit cast, but it's not anymore. Also, though the second one still acts as described, the reason given for it in the text is wrong: the system is not unable to choose among multiple alternatives. Rather, it finds *no* alternatives, again because the text-to-various-int-types casts are no longer implicit. The closest similar cases that I can find for the first example are not good replacements because they are also slated for destruction :-(. Basically, I see us moving away from the preferred-type mechanism, and perhaps eliminating it entirely soon. I'd just remove the whole of Example 7.3, I think. The mechanisms are still there, for now, but they are not invoked in any standard cases. regards, tom lane -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] to_char support for intervals
Larry Rosenman writes: I'm going to be looking into this, and was wondering what things people were looking for as formatting types. Personally, I would be looking for something that made sense. I.e., not to_char. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] to_char support for intervals
--On Thursday, March 20, 2003 17:09:18 +0100 Peter Eisentraut [EMAIL PROTECTED] wrote: Larry Rosenman writes: I'm going to be looking into this, and was wondering what things people were looking for as formatting types. Personally, I would be looking for something that made sense. I.e., not to_char. Ok, suggestions welcome.. -- Peter Eisentraut [EMAIL PROTECTED] -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [INTERFACES] Roadmap for FE/BE protocol redesign
Peter Eisentraut wrote: Bruce Momjian writes: True, but GUC seems like the way to go, and we have per-user/db settings for GUC. But the required autocommit setting depends neither on the user nor the database, it depends on the identity of the client application. That type of granularity is not offered by GUC. True, but the standard also requires autocommit off, so I can imagine folks wanting it off by default for various users/database. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] More outdated examples
OK, readded. Thanks. --- Peter Eisentraut wrote: The specific case that Tom complained about wasn't even in the latest documentation sources anymore. I think the patch should be reverted. Bruce Momjian writes: The following applied patch removes the section Tom thought wasn't needed anymore. --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I can find no suitable replacement for this example. Can anyone else? Peter Eisentraut wrote: This example in User's Guide section 7.2 doesn't work anymore at all: tgl= select @ text '-4.5' as abs; Both of the examples in this section have been obsoleted by our recent elimination of a lot of implicit casts. This one would still work if text-to-float8 were an implicit cast, but it's not anymore. Also, though the second one still acts as described, the reason given for it in the text is wrong: the system is not unable to choose among multiple alternatives. Rather, it finds *no* alternatives, again because the text-to-various-int-types casts are no longer implicit. The closest similar cases that I can find for the first example are not good replacements because they are also slated for destruction :-(. Basically, I see us moving away from the preferred-type mechanism, and perhaps eliminating it entirely soon. I'd just remove the whole of Example 7.3, I think. The mechanisms are still there, for now, but they are not invoked in any standard cases. regards, tom lane -- Peter Eisentraut [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
