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
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])
[HACKERS] Another naive question, inheritance and foreign key
Hi,
Just stumbled upon this. Is it correct to conclude that foreign keys are not
inherited from this text?
phd=# create table perbookings(type smallint) inherits (bookings);
CREATE TABLE
phd=# \d perbookings;
Table "public.perbookings"
Column |Type |Modifiers
+-+--
rid| integer |
uid| integer |
stime | timestamp without time zone |
etime | timestamp without time zone |
bid| integer | default nextval('bid_seq'::text)
type | smallint|
Check constraints: "bookings_etime" (etime > stime)
phd=# \d bookings;
Table "public.bookings"
Column |Type |Modifiers
+-+--
rid| integer |
uid| integer |
stime | timestamp without time zone |
etime | timestamp without time zone |
bid| integer | default nextval('bid_seq'::text)
Check constraints: "bookings_etime" (etime > stime)
Foreign Key constraints: $1 FOREIGN KEY (rid) REFERENCES resource(id) ON
UPDATE CASCADE ON DELETE CASCADE,
$2 FOREIGN KEY (uid) REFERENCES users(userid) ON
UPDATE CASCADE ON DELETE CASCADE
phd=# select version();
version
-
PostgreSQL 7.3.2 on i386-portbld-freebsd4.7, compiled by GCC 2.95.4
(1 row)
TIA..
Shridhar
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Primary key and references
On Tuesday 18 Mar 2003 8:07 pm, Nigel J. Andrews wrote: > On Tue, 18 Mar 2003, Shridhar Daithankar<[EMAIL PROTECTED]> wrote: > > I consider this as a bug but given my understanding of sql, I won't count > > on it. Any comments? > > If a is to be referenced in a foreign key it needs to be unique or how > could it it be known which of the rows with a given value are being refered > to. It follows that if a can be referenced in a foreign key then a uniquely > identifies a row in the referenced table and therefore a primary key of > (a,b) necessarily is unique based solely on a, i.e. the (a,b) combination > seems unlikely to be the primary key for the table. Hmm.. So I need to create unique constraint on original column. OK. Got it now.. Shridhar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Primary key and references
Hi, Today I discovered that if there is a compund primary key on a table, I can not create a reference from another table to one of the fields in the primary key.. Look at this.. phd=# create table tmp1(a integer,b integer,primary key(a,b)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'tmp1_pkey' for table 'tmp1' CREATE TABLE phd=# create table tmp2(a integer references tmp1(a)); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: UNIQUE constraint matching given keys for referenced table "tmp1" not found phd=# drop table tmp1; DROP TABLE phd=# create table tmp1(a integer unique,b integer primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'tmp1_pkey' for table 'tmp1' NOTICE: CREATE TABLE / UNIQUE will create implicit index 'tmp1_a_key' for table 'tmp1' CREATE TABLE phd=# create table tmp2(a integer references tmp1(a)); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE TABLE phd=# select version(); version - PostgreSQL 7.3.2 on i386-portbld-freebsd4.7, compiled by GCC 2.95.4 (1 row) Note that I do not require unique check on tmp2. It is perfectly acceptable to have duplicate values in table tmp2. However no duplicates are allowed in table tmp1. I consider this as a bug but given my understanding of sql, I won't count on it. Any comments? The workaround shown here is acceptable as I don't really need a compound primary key. But If I need, I know it won't work.. TIA.. Shridhar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Thread safe ecpg
Hi all, I was just wondering.The patches for making ecpg thread safe that were floating around few days back, are they going to make in any near future releases? I am badly bitten by libpq as code i my multithreaded app. is growing steadily. I find myself making stupid mistakes every now and then.Of course I don't expect any language to solve them for me but with ecpg it would be bit easier. Just thinking.. Shridhar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] location of the configuration files
On Friday 14 Feb 2003 9:05 pm, you wrote: > Martin Coxall <[EMAIL PROTECTED]> writes: > Here's a pretty topic for a flamewar: should it be /etc/postgres/ or > /etc/postgresql/ ? I vote for /etc/pgsql. Keeping in line of unix philosophy of cryptic and short names. Who wants a descriptive names anyway..:-) Seriously, the traffic on last three days ahd very high noise ratio. Especially the whole discussion of PGDATA stuff fails to register as significant IMO. Right now, I can do things the way I want to do and I guess it is pretty much same with everyone else. Is it last topic left to improve? Keep it simple and on tpoic guys. This is hackers. Keep it low volume otherwise, two years down the lines, archives will be unsearchable.. Shridhar ---(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] Changing the default configuration (was Re: [pgsql-advocacy]
On Tuesday 11 Feb 2003 10:56 pm, you wrote: > Josh Berkus <[EMAIL PROTECTED]> writes: > > What if we supplied several sample .conf files, and let the user choose > > which to copy into the database directory? We could have a "high read > > performance" profile, and a "transaction database" profile, and a > > "workstation" profile, and a "low impact" profile. > > Uh ... do we have a basis for recommending any particular sets of > parameters for these different scenarios? This could be a good idea > in the abstract, but I'm not sure I know enough to fill in the details. Let's take very simple scenario to supply pre-configured postgresql.conf. Assume that SHMMAX=Total memory/2 and supply different config files for 64MB/128Mb/256MB/512MB and above. Is it simple enough? Shridhar ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [pgsql-advocacy] [HACKERS] PostgreSQL Benchmarks
On Tuesday 11 Feb 2003 8:01 pm, Mario Weilguni wrote: > >Hrm. I just saw that the PHP ADODB guy just published a bunch of database > >benchmarks. It's fairly evident to me that benchmarking PostgreSQL on > >Win32 isn't really fair: > > > >http://php.weblogs.com/oracle_mysql_performance > > And why is the highly advocated transaction capable MySQL 4 not tested? > That's the problem, for every performance test they choose ISAM tables, and > when transactions are mentioned it's said "MySQL has transactions". But why > no benchmarks? I did benchmark mysql/postgresql/oracle sometime back. Mysql with transaction is 90% as fast as postgresql. But it dies down with increased number of users no matter how much resources you throw at it. Oracle is 130% of postgresql. This was postgresql 7.2.x series so things have changed for sure, but you got the idea, right? Shridhar ---(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] On file locking
On Friday 31 Jan 2003 9:56 am, you wrote: > Kevin Brown <[EMAIL PROTECTED]> writes: > But this only wins if a child process inheriting an open file also > inherits copies of any locks held by the parent. If not, then the > issue is moot. Anybody have any idea if file locks work that way? > Is it portable?? In my experience of HP-UX and linux, they do differ. How much, I don't remember. I have a stupid proposal. Keep file lock aside. I think shared memory can be kept alive even after process dies. Why not write a shared memory segment id to a file and let postmaster check that segment. That would be much easier. Besides file locking is implemented using setgid bit on most unices. And everybody is free to do what he/she thinks right with it. May be stupid but just a thought.. Shridhar ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [ADMIN] Cannot connect to the database (PG 7.3)
On Wednesday 29 Jan 2003 3:34 am, you wrote: > I wrote: > The reason this was done was to avoid the need to do catalog lookups > when restoring a prior setting during error recovery. That's still a > valid concern, so right offhand I don't see an easy fix. Any ideas? Document it as a bug and recommend users that do not create a numerical only user name. Agreed it is not a fix but a workaround. But it should be acceptable to most of users, isn't it? Shridhar ---(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
[HACKERS] ECPG, threading and pooling
Hi all, I would like to use ECPG as it is relatively easy to code. However my application is multithreaded and also uses connecion pools. I would like to know if ECPG is thread safe and I can use an arbitrary PGconn* object pulled from a connection pool. Do I need to use connection name? I mean how do I store connection name in an array or so? Otherwise I would have to fiddle with libpq which is bit more difficult than ECPG. TIA Shridhar ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
