Re: [SQL] need some help with a delete statement
On Monday, June 30, 2003, at 12:00 PM, scott.marlowe wrote:
On Mon, 30 Jun 2003, Matthew Hixson wrote:
On Monday, June 30, 2003, at 05:06 AM, scott.marlowe wrote:
On Fri, 27 Jun 2003, Matthew Hixson wrote:
Hi, I have a bunch of records that I need to delete from our
database.
These records represent shopping carts for visitors to our website.
The shopping carts I'd like to delete are the ones without anything
in
them. Here is the schema:
create sequence carts_sequence;
create table carts(
cart_id integer default nextval('carts_sequence') primary key,
cart_cookie varchar(24));
create sequence cart_contents_sequence;
create table cart_contents(
cart_contents_id integer default nextval('cart_contents_sequence')
primary key,
cart_id integer not null,
content_id integer not null,
expire_time timestamp);
I'm trying to use this query to delete the carts that are not
referenced from the cart_contents table.
delete from carts where cart_id in (select cart_id from carts except
(select distinct cart_id from cart_contents));
My dev machine is running Postgres 7.3.3 and is a 550Mhz Titanium
running MacOS X 10.2.6. It has 1GB of RAM. I have 266777 entries
in
v_carts and only 3746 entries in v_cart_contents. Clearly there
are a
very large number of empty carts. Running the delete statement
above
runs for over 15 minutes on this machine. I just cancelled it
because
I want to find a faster query to use in case I ever need to do this
again. While the query is running the disk does not thrash at all.
It
is definitely CPU bound.
Limiting the statement to 1 item takes about 12 seconds to run:
delete from carts where cart_id in (select cart_id from carts except
(select distinct cart_id from cart_contents) limit 1);
Time: 12062.16 ms
While in() is notoriously slow, this sounds more like a problem where
your
query is having to seq scan due to mismatching or missing indexes.
So, what kind of index do you have on cart_id,
Its is a btree index.
Table "public.carts"
Column| Type |Modifiers
-+---
+--
cart_id | integer | not null default
nextval('carts_sequence'::text)
cart_cookie | character varying(24) |
Indexes: v_carts_pkey primary key btree (cart_id),
cart_cart_cookie btree (cart_cookie)
and what happens if you:
select cart_id from carts except
(select distinct cart_id from cart_contents) limit 1;
then feed the cart_id into
explain analyze delete from carts where cart_id=id_from_above;
from psql?
#explain analyze delete from carts where cart_id=2700;
QUERY PLAN
--
--
Index Scan using carts_pkey on carts (cost=0.00..3.16 rows=1
width=6)
(actual time=162.14..162.17 rows=1 loops=1)
Index Cond: (cart_id = 2700)
Total runtime: 162.82 msec
(3 rows)
what does the output of psql say if you have the /timing switch on?
# select cart_id from carts except (select distinct cart_id from
cart_contents) limit 1;
cart_id
-
2701
(1 row)
Time: 10864.89 ms
# explain analyze delete from carts where cart_id=2701;
QUERY PLAN
Index Scan using carts_pkey on carts (cost=0.00..3.16 rows=1 width=6)
(actual time=0.50..0.52 rows=1 loops=1)
Index Cond: (cart_id = 2701)
Total runtime: 1.06 msec
(3 rows)
Time: 257.83 ms
Thanks,
-M@
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[SQL] Immutable attributes?
Hello, I have a table like this: create table test ( "test_id" serial primary key, "created" timestamp with time zone default current_timestamp check(created = current_timestamp), "some_datum" int not null ); My question concerns the "created" attribute: I want this to reflect when the tuple was craeated; and I want to make sure that the timestamp is not wrong. That will work with the above schema. However, I also want to make sure that the "crated" attribut for a tuple is not changed once it has been set. I'm thinking about implementing it through a trigger, but is there a better way to create such "immutable" attributes? /Troels ---(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: [SQL] need some help with a delete statement
On Tue, 1 Jul 2003, Matthew Hixson wrote:
>
> On Monday, June 30, 2003, at 12:00 PM, scott.marlowe wrote:
>
> > On Mon, 30 Jun 2003, Matthew Hixson wrote:
> >
> >> On Monday, June 30, 2003, at 05:06 AM, scott.marlowe wrote:
> >>
> >>> On Fri, 27 Jun 2003, Matthew Hixson wrote:
> >>>
> Hi, I have a bunch of records that I need to delete from our
> database.
> These records represent shopping carts for visitors to our website.
> The shopping carts I'd like to delete are the ones without anything
> in
> them. Here is the schema:
>
> create sequence carts_sequence;
> create table carts(
> cart_id integer default nextval('carts_sequence') primary key,
> cart_cookie varchar(24));
>
> create sequence cart_contents_sequence;
> create table cart_contents(
> cart_contents_id integer default nextval('cart_contents_sequence')
> primary key,
> cart_id integer not null,
> content_id integer not null,
> expire_time timestamp);
>
> I'm trying to use this query to delete the carts that are not
> referenced from the cart_contents table.
>
> delete from carts where cart_id in (select cart_id from carts except
> (select distinct cart_id from cart_contents));
>
> My dev machine is running Postgres 7.3.3 and is a 550Mhz Titanium
> running MacOS X 10.2.6. It has 1GB of RAM. I have 266777 entries
> in
> v_carts and only 3746 entries in v_cart_contents. Clearly there
> are a
> very large number of empty carts. Running the delete statement
> above
> runs for over 15 minutes on this machine. I just cancelled it
> because
> I want to find a faster query to use in case I ever need to do this
> again. While the query is running the disk does not thrash at all.
> It
> is definitely CPU bound.
> Limiting the statement to 1 item takes about 12 seconds to run:
>
> delete from carts where cart_id in (select cart_id from carts except
> (select distinct cart_id from cart_contents) limit 1);
> Time: 12062.16 ms
> >>>
> >>> While in() is notoriously slow, this sounds more like a problem where
> >>> your
> >>> query is having to seq scan due to mismatching or missing indexes.
> >>>
> >>> So, what kind of index do you have on cart_id,
> >>
> >> Its is a btree index.
> >>
> >> Table "public.carts"
> >> Column| Type |Modifiers
> >> -+---
> >> +--
> >> cart_id | integer | not null default
> >> nextval('carts_sequence'::text)
> >> cart_cookie | character varying(24) |
> >> Indexes: v_carts_pkey primary key btree (cart_id),
> >> cart_cart_cookie btree (cart_cookie)
> >>
> >>
> >>> and what happens if you:
> >>>
> >>> select cart_id from carts except
> >>> (select distinct cart_id from cart_contents) limit 1;
> >>>
> >>> then feed the cart_id into
> >>>
> >>> explain analyze delete from carts where cart_id=id_from_above;
> >>>
> >>> from psql?
> >>
> >> #explain analyze delete from carts where cart_id=2700;
> >> QUERY PLAN
> >> --
> >> --
> >>
> >> Index Scan using carts_pkey on carts (cost=0.00..3.16 rows=1
> >> width=6)
> >> (actual time=162.14..162.17 rows=1 loops=1)
> >> Index Cond: (cart_id = 2700)
> >> Total runtime: 162.82 msec
> >> (3 rows)
> >
> > what does the output of psql say if you have the /timing switch on?
>
> # select cart_id from carts except (select distinct cart_id from
> cart_contents) limit 1;
> cart_id
> -
> 2701
> (1 row)
> Time: 10864.89 ms
>
> # explain analyze delete from carts where cart_id=2701;
> QUERY PLAN
>
>
> Index Scan using carts_pkey on carts (cost=0.00..3.16 rows=1 width=6)
> (actual time=0.50..0.52 rows=1 loops=1)
> Index Cond: (cart_id = 2701)
> Total runtime: 1.06 msec
> (3 rows)
> Time: 257.83 ms
Well, it looks like the fks are running really slow, which may well mean
that they are seq scanning. Examine your table definition and make sure
that they are the same types on both ends, and if not, recreate the table
so that they are either the same types or one is coerced to the other when
referencing it.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Failed to initialize lc_messages to ''
Hi, I have problem start database. 1) I compile postgresql 7.3.3 with parameters. ./configure --enable-nls --enable-multibyte --enable-locale 2) Then set env variables , export LANG=cs_CZ 3) initdb -D /my_cz_db Initdb returned : The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale cs_CS. This locale setting will prevent the use of indexes for pattern matching operations. If that is a concern, rerun initdb with the collation order set to "C". For more information see the Administrator's Guide. Fixing permissions on existing directory /var/lib/postgres/data2... ok creating directory /var/lib/postgres/data2/base... ok creating directory /var/lib/postgres/data2/global... ok creating directory /var/lib/postgres/data2/pg_xlog... ok creating directory /var/lib/postgres/data2/pg_clog... ok creating template1 database in /var/lib/postgres/data2/base/1... ok creating configuration files... ok initializing pg_shadow... Failed to initialize lc_messages to '' ok enabling unlimited row size for system tables... Failed to initialize lc_messages to '' ok initializing pg_depend... Failed to initialize lc_messages to '' ok creating system views... Failed to initialize lc_messages to '' ok loading pg_description... Failed to initialize lc_messages to '' ok creating conversions... Failed to initialize lc_messages to '' ok setting privileges on built-in objects... Failed to initialize lc_messages to '' ok vacuuming database template1... Failed to initialize lc_messages to '' ok copying template1 to template0... Failed to initialize lc_messages to '' ok 4) /usr/local/pgsql/bin/postmaster -D /var/lib/postgres/my_cz_db Postmaster returned: Failed to initialize lc_messages to '' FATAL: invalid value for option 'LC_MESSAGES': 'cs_CZ' Thanks. -- Rado Petrik <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] CREATE SEQUENCE fails in plpgsql function
Erik Erkelens <[EMAIL PROTECTED]> writes: > DECLARE > new_max_records ALIAS FOR $1; > BEGIN > CREATE SEQUENCE my_sequence MAXVALUE new_max_records CYCLE; > ERROR: parser: parse error at or near "$1" at character 39 You'll need to use EXECUTE to construct and execute that CREATE SEQUENCE. Utility statements generally don't accept runtime parameters, which is what the plpgsql variable looks like to the main parser. > Also, if there is a better mechanism to implement > this, I'm all ears... There's an ALTER SEQUENCE command in CVS tip, though I'm not sure I trust it in concurrent-usage scenarios :-( regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] help with "delete joins"
On Mon, 2003-06-30 at 20:35, Josh Berkus wrote:
> Robert,
>
> > delete * from foo where not (foo.a = bar.a and foo.b=bar.b and
> > foo.c=bar.c) ;
> >
> > so i end up with
> >
> > postgres=# select * from foo;
> > a | b | c | d
> > ---+---+---+---
> > 1 | 2 | 4 | A
> > 4 | 5 | 6 | b
> > (2 rows)
> >
> > but thats not valid sql, is there some way to accomplish this?
>
> Um, your example result doesn't match your pseudo-query.
the end of a long day that started with 4 hours of sleep... no wonder I
couldn't get my head around this one. I actually did want the results of
the psuedo query, not the results I posted :-\
> Assuming that you
> want to delete everything that DOES match, not everything that DOESN'T, do:
>
> DELETE FROM foo
> WHERE EXISTS ( SELECT bar.a FROM bar
> WHERE bar.a = foo.a AND bar.b = foo.b
> AND bar.c = foo.c );
I was almost there with my original query... a NOT on your/stephan's
query gets me what I really want. :-) Thanks guys.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] CREATE SEQUENCE fails in plpgsql function
On Tue, 2003-07-01 at 13:33, Tom Lane wrote: > Erik Erkelens <[EMAIL PROTECTED]> writes: > > DECLARE > > new_max_records ALIAS FOR $1; > > BEGIN > > CREATE SEQUENCE my_sequence MAXVALUE new_max_records CYCLE; > > > ERROR: parser: parse error at or near "$1" at character 39 > > You'll need to use EXECUTE to construct and execute that CREATE > SEQUENCE. Utility statements generally don't accept runtime parameters, > which is what the plpgsql variable looks like to the main parser. > > > Also, if there is a better mechanism to implement > > this, I'm all ears... > > There's an ALTER SEQUENCE command in CVS tip, though I'm not sure > I trust it in concurrent-usage scenarios :-( It shouldn't be trusted anymore than setval() should be. That is, changes take place immediately. Seems to me you might be better off just creating a 'count' table. Update the single row when it changes. By dropping / recreating the sequence you've already blocked concurrent transactions. The single row would have less to vacuum, where the sequence has quite a bit more. Another alternative is to use setval() on the sequence BUT first pull a FOR UPDATE lock on some blocking row (for concurrency reasons). SELECT * FROM pg_class WHERE relname = 'sequence name' FOR UPDATE; SELECT setval(); This would work equally well with ALTER SEQUENCE in 7.4. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] Failed to initialize lc_messages to ''
Rado Petrik <[EMAIL PROTECTED]> writes: > export LANG=cs_CZ > Failed to initialize lc_messages to '' > FATAL: invalid value for option 'LC_MESSAGES': 'cs_CZ' Evidently your platform doesn't have complete support for setting locale to cs_CZ. Try it again with a combination like export LANG=cs_CZ export LC_MESSAGES=C regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] help with rpm script
Hello,
I am building an rpm for Mitle SME (a scaled down redhat 7.3) and have all functions
working except:
I need to have the rpm when installed create a database and a user with privilege to
that database.
These are the command functions I need to execute within the rpm...
# service postgresql start
# su postgres
# createdb account
# psql account < my1.sql
# psql account < my2.sql
# exit
But, I need these functions to be executed when someone installs my rpm. For Mysql, I
have the following
in the %post of my spec file:
---snippet-from-spec-file-
%post
# This section creates the database, dbuser, dbpasswd and # data after the package has
been installed
pw=`/bin/cat /etc/openldap/ldap.pw`
/bin/echo exit | /usr/bin/mysql --password=$pw mydb 2>&1 &> /dev/null
if [ "$?" = "1" ; then
/bin/echo "Creating mydb database..."
/usr/bin/mysqladmin --password=$pw create mydb
/bin/echo "grant all on mydb.* to [EMAIL PROTECTED] identified by 'dudepass';" |
/usr/bin/mysql --password=$pw
/usr/bin/mysql --password=$pw mydb < /path/to/my.sql
/usr/bin/mysqladmin --password=$pw reload
fi
--end-of-snippet-from-spec--
What commands could I use to complete similar needs for
a postgresql database to be created by an rpm?
Thank you for any help.
--
Best regards,
Craig Jensenmailto:[EMAIL PROTECTED]
Ace Net-Tech
http://www.acenet-tech.org/ My computer services site.
http://www.acenet-tech.org/phpBB2/ My forum, business and otherwise.
}}}All ouotgoing messages scanned and verified virus-free by Norton Antivirus
2003.{{{
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] help with "delete joins"
On Mon, Jun 30, 2003 at 18:26:38 -0400, Robert Treat <[EMAIL PROTECTED]> wrote: > what i want to do is: > > delete * from foo where not (foo.a = bar.a and foo.b=bar.b and > foo.c=bar.c) ; For the case without not (which appears to be what you really want) you can pretty much do this. See below for sample input and output. create table foo (a int, b int, c int, d text); create table bar (a int, b int, c int); insert into foo values (1,2,3,'a'); insert into foo values (1,2,4,'A'); insert into foo values (4,5,6,'b'); insert into foo values (7,8,9,'c'); insert into foo values (10,11,12,'d'); insert into bar values (1,2,3); insert into bar values (7,8,9); insert into bar values (10,11,12); delete from foo where foo.a = bar.a and foo.b=bar.b and foo.c=bar.c; select * from foo; CREATE TABLE CREATE TABLE INSERT 92443 1 INSERT 92444 1 INSERT 92445 1 INSERT 92446 1 INSERT 92447 1 INSERT 92448 1 INSERT 92449 1 INSERT 92450 1 DELETE 3 a | b | c | d ---+---+---+--- 1 | 2 | 4 | A 4 | 5 | 6 | b (2 rows) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Immutable attributes?
Hello, On Tue, 2003-07-01 at 18:28, Robert Treat <[EMAIL PROTECTED]> wrote: > > want to make sure that the "crated" attribut for a tuple is > > not changed once it has been set. > > > > I'm thinking about implementing it through a trigger, but is there a > > better way to create such "immutable" attributes? > > I don't know if it's "better", but this is one of the things people find > the RULE system really handy for. I thought about using the rule system for that. However: - I would like to be able to throw an exception if an immutable attribute is changed; it seems that can't be done with the rule system(?) - it seems that RULEs are a PostgreSQL-only phenomenon; I try to keep my SQL more portable than that > Check the docs, I believe there are examples of this. I haven't been able to find any related examples. Anyways, I have now found a way to implement my immutable timestamp fields using a stored procedure and a trigger: create function create_time_unchanged() returns trigger as ' begin if old.time_created <> new.time_created then raise exception ''time_created may not be changed: % <> %'', old.time_created, new.time_created ; end if; return new; end;' language 'plpgsql'; create trigger ensure_create_time_unchanged before update on transaction_pbs for each row execute procedure create_time_unchanged(); Now, let's say that the "transaction" relation has a field "time_created" of type timestamp with time zone and that a record with time_created=2003-07-01 20:56:11.393664+02 : => update transaction => set time_created='2003-07-01 20:56:11.393664+02'::timestamptz => where order_id=1000; -- NOTE: No change. UPDATE 1 => update transaction => set time_created='2003-07-01 20:56:00+02'::timestamptz => where order_id=1000; -- NOTE: Changed. ERROR: time_created may not be changed: 2003-07-01 20:56:11.393664+02 <> 2003-07-01 20:56:00+02 So things work. -- Troels Arvin <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Failed to initialize lc_messages to ''
It seems that one of the other locale variables (LC_ALL?) still contains values that are not accepted. Note that initdb reports about cs_CS, which is not what you set. Rado Petrik writes: > Hi, > I have problem start database. > > 1) I compile postgresql 7.3.3 with parameters. > > ./configure --enable-nls --enable-multibyte --enable-locale > > 2) Then set env variables , > > export LANG=cs_CZ > > 3) initdb -D /my_cz_db > > Initdb returned : > > The files belonging to this database system will be owned by user > "postgres". > This user must also own the server process. > > The database cluster will be initialized with locale cs_CS. > This locale setting will prevent the use of indexes for pattern matching > operations. If that is a concern, rerun initdb with the collation order > set to "C". For more information see the Administrator's Guide. > > Fixing permissions on existing directory /var/lib/postgres/data2... ok > creating directory /var/lib/postgres/data2/base... ok > creating directory /var/lib/postgres/data2/global... ok > creating directory /var/lib/postgres/data2/pg_xlog... ok > creating directory /var/lib/postgres/data2/pg_clog... ok > creating template1 database in /var/lib/postgres/data2/base/1... ok > creating configuration files... ok > initializing pg_shadow... Failed to initialize lc_messages to '' > ok > enabling unlimited row size for system tables... Failed to initialize > lc_messages to '' > ok > initializing pg_depend... Failed to initialize lc_messages to '' > ok > creating system views... Failed to initialize lc_messages to '' > ok > loading pg_description... Failed to initialize lc_messages to '' > ok > creating conversions... Failed to initialize lc_messages to '' > ok > setting privileges on built-in objects... Failed to initialize > lc_messages to '' > ok > vacuuming database template1... Failed to initialize lc_messages to '' > ok > copying template1 to template0... Failed to initialize lc_messages to '' > ok > > > 4) /usr/local/pgsql/bin/postmaster -D /var/lib/postgres/my_cz_db > > Postmaster returned: > > Failed to initialize lc_messages to '' > FATAL: invalid value for option 'LC_MESSAGES': 'cs_CZ' > > Thanks. > > -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Immutable attributes?
On Tue, 2003-07-01 at 05:59, Troels Arvin wrote:
> Hello,
>
> I have a table like this:
>
> create table test (
> "test_id" serial primary key,
> "created" timestamp with time zone
> default current_timestamp
> check(created = current_timestamp),
> "some_datum" int not null
> );
>
> My question concerns the "created" attribute: I want this to reflect when
> the tuple was craeated; and I want to make sure that the timestamp is not
> wrong. That will work with the above schema. However, I also want to make
> sure that the "crated" attribut for a tuple is not changed once it has
> been set.
>
> I'm thinking about implementing it through a trigger, but is there a
> better way to create such "immutable" attributes?
>
I don't know if it's "better", but this is one of the things people find
the RULE system really handy for. Check the docs, I believe there are
examples of this.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] LEAST and GREATEST functions?
Josh Berkus <[EMAIL PROTECTED]> writes: > Stefan, > > > I know the LEAST and GREATEST functions are not part > > of standard SQL, but they sure were handy where I came > > from (Oracle-land). > > Um, what's wrong with MAX and MIN, exactly? MAX and MIN are single-parameter aggregate functions. LEAST and GREATEST are two-parameter (though in postgres they could be defined for 3 and more parameters) scalar functions. eg: SELECT max(a) FROM bar would return a single tuple with the maximum value of a from amongst every record. whereas: SELECT greatest(a,b) FROM bar would return one tuple for every record in the table with a single value representing the greater of bar.a and bar.b. You could define your own functions to do this but it would be tiresome to define one for every datatype. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] need some help with a delete statement
On Tuesday, July 1, 2003, at 05:47 AM, scott.marlowe wrote:
what does the output of psql say if you have the /timing switch on?
# select cart_id from carts except (select distinct cart_id from
cart_contents) limit 1;
cart_id
-
2701
(1 row)
Time: 10864.89 ms
# explain analyze delete from carts where cart_id=2701;
QUERY PLAN
--
--
Index Scan using carts_pkey on carts (cost=0.00..3.16 rows=1
width=6)
(actual time=0.50..0.52 rows=1 loops=1)
Index Cond: (cart_id = 2701)
Total runtime: 1.06 msec
(3 rows)
Time: 257.83 ms
Well, it looks like the fks are running really slow, which may well
mean
that they are seq scanning. Examine your table definition and make
sure
that they are the same types on both ends, and if not, recreate the
table
so that they are either the same types or one is coerced to the other
when
referencing it.
Here are my table definitions.
# \d v_carts;
Table "public.carts"
Column| Type |Modifiers
-+---
+--
cart_id | integer | not null default
nextval('carts_sequence'::text)
cart_cookie | character varying(24) |
Indexes: carts_pkey primary key btree (cart_id),
cart_cart_cookie btree (cart_cookie)
# \d cart_contents;
Table "public.cart_contents"
Column |Type |
Modifiers
--+-
+--
cart_contents_id | integer | not null default
nextval('cart_contents_sequence'::text)
cart_id | integer | not null
content_id | integer | not null
expire_time | timestamp without time zone |
Indexes: cart_contents_pkey primary key btree (cart_contents_id),
cart_contents_cart_id btree (cart_id),
cart_contents_content_id btree (content_id)
The fk cart_contents.cart_id points to the pk carts.cart_id, and they
are both integers.
-M@
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] LEAST and GREATEST functions?
On Tue, Jul 01, 2003 at 12:29:16 -0400, Greg Stark <[EMAIL PROTECTED]> wrote: > > SELECT greatest(a,b) FROM bar > > would return one tuple for every record in the table with a single value > representing the greater of bar.a and bar.b. You can do this with case. SELECT CASE WHEN a >= b THEN a ELSE b END FROM bar; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] need some help with a delete statement
On Tue, 1 Jul 2003, Matthew Hixson wrote:
>
> On Tuesday, July 1, 2003, at 05:47 AM, scott.marlowe wrote:
> >>>
> >>> what does the output of psql say if you have the /timing switch on?
> >>
> >> # select cart_id from carts except (select distinct cart_id from
> >> cart_contents) limit 1;
> >> cart_id
> >> -
> >> 2701
> >> (1 row)
> >> Time: 10864.89 ms
> >>
> >> # explain analyze delete from carts where cart_id=2701;
> >> QUERY PLAN
> >> --
> >> --
> >>
> >> Index Scan using carts_pkey on carts (cost=0.00..3.16 rows=1
> >> width=6)
> >> (actual time=0.50..0.52 rows=1 loops=1)
> >> Index Cond: (cart_id = 2701)
> >> Total runtime: 1.06 msec
> >> (3 rows)
> >> Time: 257.83 ms
> >
> > Well, it looks like the fks are running really slow, which may well
> > mean
> > that they are seq scanning. Examine your table definition and make
> > sure
> > that they are the same types on both ends, and if not, recreate the
> > table
> > so that they are either the same types or one is coerced to the other
> > when
> > referencing it.
>
> Here are my table definitions.
>
> # \d v_carts;
> Table "public.carts"
> Column| Type |Modifiers
> -+---
> +--
> cart_id | integer | not null default
> nextval('carts_sequence'::text)
> cart_cookie | character varying(24) |
> Indexes: carts_pkey primary key btree (cart_id),
> cart_cart_cookie btree (cart_cookie)
>
> # \d cart_contents;
>Table "public.cart_contents"
>Column |Type |
> Modifiers
> --+-
> +--
> cart_contents_id | integer | not null default
> nextval('cart_contents_sequence'::text)
> cart_id | integer | not null
> content_id | integer | not null
> expire_time | timestamp without time zone |
> Indexes: cart_contents_pkey primary key btree (cart_contents_id),
> cart_contents_cart_id btree (cart_id),
> cart_contents_content_id btree (content_id)
>
>
> The fk cart_contents.cart_id points to the pk carts.cart_id, and they
> are both integers.
Try reindexing cart_contents_pkey and carts_pkey and see if that helps.
You may have index growth problems. Just guessing.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] LEAST and GREATEST functions?
Greg Stark wrote:
SELECT greatest(a,b) FROM bar
would return one tuple for every record in the table with a single value
representing the greater of bar.a and bar.b.
You could define your own functions to do this but it would be tiresome to
define one for every datatype.
In 7.4devel (just starting beta) you can do this:
create or replace function greatest(anyelement, anyelement) returns
anyelement as 'select case when $1 > $2 then $1 else $2 end' language 'sql';
regression=# select greatest(1, 2);
greatest
--
2
(1 row)
regression=# select greatest('b'::text, 'a');
greatest
--
b
(1 row)
regression=# select greatest(now(), 'yesterday');
greatest
---
2003-07-01 13:21:56.506106-07
(1 row)
The cast to text is needed because 'a' and 'b' are really typed as
unknown, and with polymorphic functions, you need a well defined data type.
So if you had a table:
create table g(f1 text, f2 text);
insert into g values ('a','b');
insert into g values ('c','b');
regression=# select greatest(f1, f2) from g;
greatest
--
b
c
(2 rows)
Doesn't help for 7.3.x, but at least you know help is on the way ;-)
Of course, you could always just use the case statement.
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] LEAST and GREATEST functions?
> Um, what's wrong with MAX and MIN, exactly? MIN and MAX are aggregate functions, LEAST and GREATEST are not. See the examples on the following table: foo A B - - 1 4 2 3 3 2 > SELECT LEAST(a, b), GREATEST(a, b) FROM foo; LEAST(a, b) GREATEST(a, b) --- -- 1 4 2 3 2 3 > SELECT MIN(a), MAX(b) FROM foo; MIN(a) MAX(b) -- -- 1 4 After further research, I found that the only way to have a function with a variable number of arguments is to create N number of overloaded functions, e.g. CREATE FUNCTION least(int)... CREATE FUNCTION least(int, int)... CREATE FUNCTION least(int, int, int)... ...etc... That sucks, especially since the underlying languages support variable arguments that will scale to who-knows-where (see varargs in C, *args in Python, for starters). Not only that, but I'd have to create another N number of functions for different datatypes (int, float, date, etc.). In addition to adding the LEAST and GREATEST functions, the PostgreSQL developers should add the ability to create user-defined functions with a variable number of arguments. Cheers, -Stefan __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] mergejoin error message executing in 7.2
hi there, I have a problem trying to execute a complex query that was designed in 7.3.2 to a machine running 7.2.x this is the error message FULL JOIN is only supported with mergejoinable join conditions this is the source string for a jdbc prepared statement. (it has ordering clause added at the end using string concatenation) this string works fine in 7.3.2 but fails runtime in 7.2. Does anyone know what the problem is? I can't find the message on the net anywhere except in the 'C' source on postgres's developer channel or some japanese site (sigh). strSQL ="SELECT stock," + " p.description," + " p.stockgroup," + " o.ordercurrent," + " o.type," + " s.quantity," + " a.ordercurrent, " + " a.type " + " FROM " + " (SELECT stock,ordercurrent,type,line " + " FROM orderlines o " + " WHERE o.theorder = ? " + " AND (o.TYPE='P' OR o.TYPE='T') ) AS o" + " FULL OUTER JOIN " + " (SELECT DISTINCT ON (stock) stock,quantity " + " FROM standingorders s " + " WHERE s.account = ? " + " AND s.dayno = ? " + " AND s.delivery = ? "+ " AND commencedate <= ? " + " ORDER BY stock, commencedate DESC) AS s " + " USING (stock) " + " FULL OUTER JOIN " + " (SELECT stock,ordercurrent,type " + " FROM orderlines ol " + " WHERE ol.theorder = ? " + " AND ol.TYPE<>'P' " + " AND ol.TYPE<>'T' ) AS a " + " USING (stock) " + " INNER JOIN stockitems p ON (p.id=stock) " + " WHERE p.status='N' " + " ORDER BY "; Any help greatly appreciated. Regards, Kevin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] passing a record as a function argument in pl/pgsql
Is it possible? From what I tried it is possible to create such a function but it is not possible to call it ?! Can anyone provide an example? Tnx.
Re: [SQL] mergejoin error message executing in 7.2
"kevin rowe" <[EMAIL PROTECTED]> writes: > I have a problem trying to execute a complex query that was designed in > 7.3.2 to a machine running 7.2.x > this is the error message > FULL JOIN is only supported with mergejoinable join conditions Nested FULL JOINs don't work in 7.2.*. The fix is not practical to back-port, so you're stuck: either modify the query to avoid that, or update the machine with the older server. 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: [SQL] LEAST and GREATEST functions?
Joe Conway wrote: In 7.4devel (just starting beta) you can do this: Actually to correct myself, we just started "feature freeze" for 7.4, with beta planned to start on or about July 15th. Sorry for any confusion caused. Joe ---(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: [SQL] LEAST and GREATEST functions?
Joe, > create or replace function greatest(anyelement, anyelement) returns > anyelement as 'select case when $1 > $2 then $1 else $2 end' language > 'sql'; Way cool. I'd have to imagine that it would blow up if you did this, though: select greatest ( 512, now() ); With an "Operator is not defined" error, hey? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] LEAST and GREATEST functions?
Josh Berkus wrote: create or replace function greatest(anyelement, anyelement) returns anyelement as 'select case when $1 > $2 then $1 else $2 end' language 'sql'; Way cool. I'd have to imagine that it would blow up if you did this, though: select greatest ( 512, now() ); With an "Operator is not defined" error, hey? It errors out with a type mismatch error: regression=# select greatest (512, now()); ERROR: Function greatest(integer, timestamp with time zone) does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts Of course none of this is documented yet (because I still owe the documentation ;-), but that can be done during feature freeze/beta), but the concept of the anyelement data type is that, although it can mean literally any data type, any arguments (or return type) so defined have to match each other at function call time. So with: greatest(anyelement, anyelement) returns anyelement when it gets called, the two arguments *must* be the same data type, and the function will return the same type. Any arguments declared with a specific datatype (say integer) don't participate in the runtime resolution of the polymorphic arguments. Similarly there is an anyarray data type that is constrained at runtime to be an array of anything that was defined as anyelement; e.g.: create or replace function myelement(anyarray, int) returns anyelement as 'select $1[$2]' language 'sql'; regression=# select myelement(array[11,22,33,44,55], 2); myelement --- 22 (1 row) Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] passing a record as a function argument in pl/pgsql
Alon Noy wrote: From what I tried it is possible to create such a function but it is not possible to call it ?! Can anyone provide an example? create table foo (f1 int, f2 text); insert into foo values(1,'a'); insert into foo values(2,'b'); insert into foo values(3,'c'); create or replace function get_foo(int) returns foo as 'select * from foo where f1 = $1' language 'sql'; create or replace function use_foo(foo) returns text as ' declare v_foo alias for $1; begin return v_foo.f2; end; ' language 'plpgsql'; regression=# select use_foo(get_foo(2)); use_foo - b (1 row) HTH, Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] columnar format
good day! i would like to ask for the sql statement to the output: ITEM DESC Jan Feb Mar Apr ... Sep Total xx 999 999 999 999 ... 999 9,999 where "Jan" column is sum of all "x" items purchased on Jan, "Feb" column as sum of Feb purchases, and so on up to "Sep", and "Total" as the total from "Jan" to "Sep" (as in the case here). the numbers may/may not be formatted, and the period varies, depending on the range. your help will be much appreciated. thanks!Ü _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Timeout for lock table
Dear All, I begin a transaction. I then execute a SQL command "SELECT FOR UPDATE" to lock some records on the first session. Besides, I also lock some rows using the same method on the second session. Is it possible to return an error message to indicate that some records were locked by another user when I lock it on the second session? Please help me, THX!! JMAN
Re: [SQL] LEAST and GREATEST functions?
Greg Stark wrote: MAX and MIN are single-parameter aggregate functions. LEAST and GREATEST are two-parameter (though in postgres they could be defined for 3 and more parameters) scalar functions. If LEAST and GREATEST can accept any number of parameters, wouldn't it make sense to code it like the way COALESCE works, rather than defining a function for it? This way we don't need define all the various functions with different types. e.g. SELECT greatest(a, b, c) FROM bar; becomes SELECT greatest(a, greatest(b, c)) from bar; becomes SELECT CASE WHEN b < c THEN CASE WHEN c < a THEN a ELSE c END ELSE CASE WHEN b < a THEN a ELSE b END END FROM bar; From the docs: COALESCE and NULLIF are just shorthand for CASE expressions. They are actually converted into CASE expressions at a very early stage of processing, and subsequent processing thinks it is dealing with CASE. Thus an incorrect COALESCE or NULLIF usage may draw an error message that refers to CASE. -- Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 GNU/Linux 11:30am up 188 days, 2:35, 5 users, load average: 5.19, 5.08, 5.02 pgp0.pgp Description: PGP signature
[SQL] Break referential integrity.
Hi, I know that if you have a trigger and function then drop/replace the function the trigger needs to be drop/replaced too so that it can see the new function. Is it the same for Ref. Integ. on table's too ? If table B's foreign key references table A and you drop/replace table A then the reference from table B to table A is broken and needs to be recreated ? Hope I explained that OK. Thanks Rudi. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Break referential integrity.
On Wed, 2 Jul 2003, Rudi Starcevic wrote: > Hi, > > I know that if you have a trigger and function then drop/replace the > function the trigger needs > to be drop/replaced too so that it can see the new function. > > Is it the same for Ref. Integ. on table's too ? > > If table B's foreign key references table A and you drop/replace table A > then the reference from table B to table A is broken and needs to be > recreated ? In recent versions, you should not be drop table A without specifying cascade which will drop the constraint for you (and thus you'll need to recreate it). In older versions, I'm not 100% sure what'd happen, but you probably should drop and recreate it for good measure. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] LEAST and GREATEST functions?
Ang Chin Han <[EMAIL PROTECTED]> writes: > If LEAST and GREATEST can accept any number of parameters, wouldn't it > make sense to code it like the way COALESCE works, rather than defining > a function for it? This way we don't need define all the various > functions with different types. But COALESCE is a special feature hard-wired into the parser. There's no free lunch --- you pay for your extensibility somewhere. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] About Postgresql Service on SUN OS
Hello, Any one who knows, how to start the postgresql service on sun solaris server. Please help me in this regards. Thank you. Regards, Atul Pedgaonkar ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] LEAST and GREATEST functions?
Tom Lane wrote: But COALESCE is a special feature hard-wired into the parser. There's no free lunch --- you pay for your extensibility somewhere. That's what I'm suggesting: hard-wiring LEAST and GREATEST into the parser. 7.5, maybe? The question is: is it worth hard-wiring vs functions? (time passes) Doesn't seem to be in SQL92. It's in Oracle, Interbase and MySQL, though, says google. I'd say we need to have LEAST and GREATEST at least somewhere in contrib (as functions) if not core, to make transition from other RDBMS to postgresql easier. A brief test shows that we would incur quite a performance penalty (I compared COALESCE with coalesce_sql_function) if it isn't hardwiring. -- Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 GNU/Linux 1:30pm up 188 days, 4:35, 4 users, load average: 5.03, 5.06, 5.08 pgp0.pgp Description: PGP signature
[SQL] SQL
Dear All, May I execute a SQL to find out user's information (e.g. user id or ip address) who are connecting to a database? THX!! JMAN
Re: [SQL] LEAST and GREATEST functions?
Ang Chin Han <[EMAIL PROTECTED]> writes: > I'd say we need to have LEAST and GREATEST at least somewhere in contrib > (as functions) if not core, to make transition from other RDBMS to > postgresql easier. > A brief test shows that we would incur quite a performance penalty (I > compared COALESCE with coalesce_sql_function) if it isn't hardwiring. In 7.4 I think that tradeoff will change significantly. SQL functions are polymorphic thanks to Joe Conway, and they're inline-able thanks to me ;-), so there's really no difference between writing the strictly SQL-compliant SELECT CASE WHEN a>b THEN a ELSE b END FROM foo; and writing create function greatest(anyelement, anyelement) returns anyelement as 'select case when $1>$2 then $1 else $2 end' language sql; SELECT greatest(a,b) FROM foo; You do have to create several greatest() functions for different numbers of arguments, but not one for each datatype you want to handle. I have not seen enough requests for a native LEAST/GREATEST implementation to make me think we need to do more than this... certainly I'd rather spend development effort on general facilities like polymorphism and inlining than on creating one-use facilities like built-in LEAST/GREATEST. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
