pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedinsert
19+ messages / 13 participants
[nested] [flat]
* insert
@ 2004-08-13 07:47 Ulrich Wisser <ulrich.wisser@relevanttraffic.se>
0 siblings, 0 replies; 19+ messages in thread
From: Ulrich Wisser @ 2004-08-13 07:47 UTC (permalink / raw)
To: pgsql-performance
Hi,
is there anything I can doo to speed up inserts? One of my tables gets
about 100 new rows every five minutes. And somehow the inserts tend to
take more and more time.
Any suggestions welcome.
TIA
Ulrich
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: insert
@ 2004-08-13 10:08 G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
2004-08-13 10:49 ` Re: insert gnari <gnari@simnet.is>
0 siblings, 1 reply; 19+ messages in thread
From: G u i d o B a r o s i o @ 2004-08-13 10:08 UTC (permalink / raw)
To: ulrich.wisser@relevanttraffic.se; pgsql-performance
Tips!
*Delete indexes and recreate them after the insert.
*Disable auto-commit
*Perform a copy will be faster, sure.
Best wishes,
Guido
> Hi,
>
> is there anything I can doo to speed up inserts? One of my tables gets
> about 100 new rows every five minutes. And somehow the inserts tend to
> take more and more time.
>
> Any suggestions welcome.
>
> TIA
>
> Ulrich
>
>
> ---------------------------(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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: insert
2004-08-13 10:08 Re: insert G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
@ 2004-08-13 10:49 ` gnari <gnari@simnet.is>
2004-08-14 14:24 ` Re: insert Gaetano Mendola <mendola@bigfoot.com>
0 siblings, 1 reply; 19+ messages in thread
From: gnari @ 2004-08-13 10:49 UTC (permalink / raw)
To: pgsql-performance
"G u i d o B a r o s i o" <gbarosio@uolsinectis.com.ar> wrote:
[speeding up 100 inserts every 5 minutes]
> Tips!
> *Delete indexes and recreate them after the insert.
sounds a bit extreme, for only 100 inserts
gnari
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: insert
2004-08-13 10:08 Re: insert G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
2004-08-13 10:49 ` Re: insert gnari <gnari@simnet.is>
@ 2004-08-14 14:24 ` Gaetano Mendola <mendola@bigfoot.com>
0 siblings, 0 replies; 19+ messages in thread
From: Gaetano Mendola @ 2004-08-14 14:24 UTC (permalink / raw)
To: pgsql-performance
gnari wrote:
> "G u i d o B a r o s i o" <gbarosio@uolsinectis.com.ar> wrote:
>
> [speeding up 100 inserts every 5 minutes]
>
>
>>Tips!
>>*Delete indexes and recreate them after the insert.
>
>
> sounds a bit extreme, for only 100 inserts
which fsync method are you using ?
change it and see what happen
Regards
Gaetano Mendola
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: insert
@ 2004-08-13 11:16 G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
2004-08-13 11:47 ` Re: insert gnari <gnari@simnet.is>
0 siblings, 1 reply; 19+ messages in thread
From: G u i d o B a r o s i o @ 2004-08-13 11:16 UTC (permalink / raw)
To: gnari@simnet.is; pgsql-performance
As I see it's 100 inserts every 5 minutes, not only 100 inserts.
Sure it's extreme for only 100 inserts.
Cheers,
Guido
> "G u i d o B a r o s i o" <gbarosio@uolsinectis.com.ar> wrote:
>
> [speeding up 100 inserts every 5 minutes]
>
> > Tips!
> > *Delete indexes and recreate them after the insert.
>
> sounds a bit extreme, for only 100 inserts
>
> gnari
>
>
>
>
>
> ---------------------------(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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: insert
2004-08-13 11:16 Re: insert G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
@ 2004-08-13 11:47 ` gnari <gnari@simnet.is>
2004-08-13 12:10 ` Re: insert Ulrich Wisser <ulrich.wisser@relevanttraffic.se>
0 siblings, 1 reply; 19+ messages in thread
From: gnari @ 2004-08-13 11:47 UTC (permalink / raw)
To: gbarosio@uolsinectis.com.ar; pgsql-performance
From: "G u i d o B a r o s i o" <gbarosio@uolsinectis.com.ar>:
> As I see it's 100 inserts every 5 minutes, not only 100 inserts.
>
> Sure it's extreme for only 100 inserts.
I am sorry, I do not quite grasp what you are saying.
my understanding was that there are constantly new inserts,
coming in bursts of 100 , every 5 minutes.
I imagined that the indexes were needed in between.
if this is the case, the bunches of 100 inserts should
be done inside a transaction (or by 1 COPY statement)
if, on the other hand, the inserts happen independently,
at a rate of 100 inserts / 5 minutes, then this will not help
gnari
>
> Cheers,
> Guido
>
> > "G u i d o B a r o s i o" <gbarosio@uolsinectis.com.ar> wrote:
> >
> > [speeding up 100 inserts every 5 minutes]
> >
> > > Tips!
> > > *Delete indexes and recreate them after the insert.
> >
> > sounds a bit extreme, for only 100 inserts
> >
> > gnari
> >
> >
> >
> >
> >
> > ---------------------------(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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: insert
2004-08-13 11:16 Re: insert G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
2004-08-13 11:47 ` Re: insert gnari <gnari@simnet.is>
@ 2004-08-13 12:10 ` Ulrich Wisser <ulrich.wisser@relevanttraffic.se>
2004-08-13 12:41 ` Re: insert Paul Thomas <paul@tmsl.demon.co.uk>
2004-08-13 12:57 ` Re: insert Rod Taylor <pg@rbt.ca>
0 siblings, 2 replies; 19+ messages in thread
From: Ulrich Wisser @ 2004-08-13 12:10 UTC (permalink / raw)
To: gnari <gnari@simnet.is>; +Cc: pgsql-performance
Hi,
my inserts are done in one transaction, but due to some foreign key
constraints and five indexes sometimes the 100 inserts will take more
than 5 minutes.
/Ulrich
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: insert
2004-08-13 11:16 Re: insert G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
2004-08-13 11:47 ` Re: insert gnari <gnari@simnet.is>
2004-08-13 12:10 ` Re: insert Ulrich Wisser <ulrich.wisser@relevanttraffic.se>
@ 2004-08-13 12:41 ` Paul Thomas <paul@tmsl.demon.co.uk>
1 sibling, 0 replies; 19+ messages in thread
From: Paul Thomas @ 2004-08-13 12:41 UTC (permalink / raw)
To: Ulrich Wisser <ulrich.wisser@relevanttraffic.se>; +Cc: pgsql-performance; pgsql-performance
On 13/08/2004 13:10 Ulrich Wisser wrote:
> Hi,
>
> my inserts are done in one transaction, but due to some foreign key
> constraints and five indexes sometimes the 100 inserts will take more
> than 5 minutes.
Two possibilities come to mind:
a) you need an index on the referenced FK field
b) you have an index but a type mis-match (e.g, an int4 field referencing
an int8 field)
Either of these will cause a sequential table scan and poor performance.
--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: insert
2004-08-13 11:16 Re: insert G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
2004-08-13 11:47 ` Re: insert gnari <gnari@simnet.is>
2004-08-13 12:10 ` Re: insert Ulrich Wisser <ulrich.wisser@relevanttraffic.se>
@ 2004-08-13 12:57 ` Rod Taylor <pg@rbt.ca>
2004-08-13 15:02 ` Re: insert Bruno Wolff III <bruno@wolff.to>
1 sibling, 1 reply; 19+ messages in thread
From: Rod Taylor @ 2004-08-13 12:57 UTC (permalink / raw)
To: Ulrich Wisser <ulrich.wisser@relevanttraffic.se>; +Cc: gnari <gnari@simnet.is>; pgsql-performance
On Fri, 2004-08-13 at 08:10, Ulrich Wisser wrote:
> Hi,
>
> my inserts are done in one transaction, but due to some foreign key
> constraints and five indexes sometimes the 100 inserts will take more
> than 5 minutes.
It is likely that you are missing an index on one of those foreign key'd
items.
Do an EXPLAIN ANALYZE SELECT * FROM foreign_table WHERE foreign_col =
'<insert value>';
Fix them until they're quick.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: insert
2004-08-13 11:16 Re: insert G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
2004-08-13 11:47 ` Re: insert gnari <gnari@simnet.is>
2004-08-13 12:10 ` Re: insert Ulrich Wisser <ulrich.wisser@relevanttraffic.se>
2004-08-13 12:57 ` Re: insert Rod Taylor <pg@rbt.ca>
@ 2004-08-13 15:02 ` Bruno Wolff III <bruno@wolff.to>
2004-08-13 16:17 ` Re: insert Matt Clark <matt@ymogen.net>
2004-08-13 18:01 ` Re: insert Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 19+ messages in thread
From: Bruno Wolff III @ 2004-08-13 15:02 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: Ulrich Wisser <ulrich.wisser@relevanttraffic.se>; gnari <gnari@simnet.is>; pgsql-performance
On Fri, Aug 13, 2004 at 08:57:56 -0400,
Rod Taylor <pg@rbt.ca> wrote:
> On Fri, 2004-08-13 at 08:10, Ulrich Wisser wrote:
> > Hi,
> >
> > my inserts are done in one transaction, but due to some foreign key
> > constraints and five indexes sometimes the 100 inserts will take more
> > than 5 minutes.
>
> It is likely that you are missing an index on one of those foreign key'd
> items.
I don't think that is too likely as a foreign key reference must be a
unique key which would have an index. I think the type mismatch
suggestion is probably what the problem is.
The current solution is to make the types match. In 8.0.0 it would probably
work efficiently as is, though it isn't normal for foreign keys to have a type
mismatch and he may want to change that anyway.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: insert
2004-08-13 11:16 Re: insert G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
2004-08-13 11:47 ` Re: insert gnari <gnari@simnet.is>
2004-08-13 12:10 ` Re: insert Ulrich Wisser <ulrich.wisser@relevanttraffic.se>
2004-08-13 12:57 ` Re: insert Rod Taylor <pg@rbt.ca>
2004-08-13 15:02 ` Re: insert Bruno Wolff III <bruno@wolff.to>
@ 2004-08-13 16:17 ` Matt Clark <matt@ymogen.net>
2004-08-13 16:31 ` Re: insert Bruno Wolff III <bruno@wolff.to>
1 sibling, 1 reply; 19+ messages in thread
From: Matt Clark @ 2004-08-13 16:17 UTC (permalink / raw)
To: 'Bruno Wolff III' <bruno@wolff.to>; 'Rod Taylor' <pg@rbt.ca>; +Cc: 'Ulrich Wisser' <ulrich.wisser@relevanttraffic.se>; 'gnari' <gnari@simnet.is>; pgsql-performance
> > It is likely that you are missing an index on one of those foreign
> > key'd items.
>
> I don't think that is too likely as a foreign key reference
> must be a unique key which would have an index.
I think you must be thinking of primary keys, not foreign keys. All
one-to-many relationships have non-unique foreign keys.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: insert
2004-08-13 11:16 Re: insert G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
2004-08-13 11:47 ` Re: insert gnari <gnari@simnet.is>
2004-08-13 12:10 ` Re: insert Ulrich Wisser <ulrich.wisser@relevanttraffic.se>
2004-08-13 12:57 ` Re: insert Rod Taylor <pg@rbt.ca>
2004-08-13 15:02 ` Re: insert Bruno Wolff III <bruno@wolff.to>
2004-08-13 16:17 ` Re: insert Matt Clark <matt@ymogen.net>
@ 2004-08-13 16:31 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 19+ messages in thread
From: Bruno Wolff III @ 2004-08-13 16:31 UTC (permalink / raw)
To: Matt Clark <matt@ymogen.net>; +Cc: Rod Taylor <pg@rbt.ca>; Ulrich Wisser <ulrich.wisser@relevanttraffic.se>; gnari <gnari@simnet.is>; pgsql-performance
On Fri, Aug 13, 2004 at 17:17:10 +0100,
Matt Clark <matt@ymogen.net> wrote:
> > > It is likely that you are missing an index on one of those foreign
> > > key'd items.
> >
> > I don't think that is too likely as a foreign key reference
> > must be a unique key which would have an index.
>
> I think you must be thinking of primary keys, not foreign keys. All
> one-to-many relationships have non-unique foreign keys.
The target of the reference needs to have at least a unique index.
I am not sure if it needs to actually be declared as either a unique
or primary key, though that is the intention.
The records doing the referencing don't need (and normally aren't)
unique.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: insert
2004-08-13 11:16 Re: insert G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
2004-08-13 11:47 ` Re: insert gnari <gnari@simnet.is>
2004-08-13 12:10 ` Re: insert Ulrich Wisser <ulrich.wisser@relevanttraffic.se>
2004-08-13 12:57 ` Re: insert Rod Taylor <pg@rbt.ca>
2004-08-13 15:02 ` Re: insert Bruno Wolff III <bruno@wolff.to>
@ 2004-08-13 18:01 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 19+ messages in thread
From: Tom Lane @ 2004-08-13 18:01 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Rod Taylor <pg@rbt.ca>; Ulrich Wisser <ulrich.wisser@relevanttraffic.se>; gnari <gnari@simnet.is>; pgsql-performance
Bruno Wolff III <bruno@wolff.to> writes:
> Rod Taylor <pg@rbt.ca> wrote:
>> It is likely that you are missing an index on one of those foreign key'd
>> items.
> I don't think that is too likely as a foreign key reference must be a
> unique key which would have an index. I think the type mismatch
> suggestion is probably what the problem is.
I agree. It is possible to have a lack-of-index problem on the
referencing column (as opposed to the referenced column), but that
normally only hurts you for deletes from the referenced table.
> The current solution is to make the types match. In 8.0.0 it would probably
> work efficiently as is, though it isn't normal for foreign keys to have a type
> mismatch and he may want to change that anyway.
8.0 will not fix this particular issue, as I did not add any numeric-vs-int
comparison operators. If we see a lot of complaints we could think
about adding such, but for 8.0 only the more common cases such as
int-vs-bigint are covered.
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* insert
@ 2011-07-23 16:23 alan <alan.miller3@gmail.com>
2011-07-25 14:34 ` Re: insert alan <alan.miller3@gmail.com>
2011-07-29 20:14 ` Re: insert Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 19+ messages in thread
From: alan @ 2011-07-23 16:23 UTC (permalink / raw)
To: pgsql-performance
next question.
I have a product table with a 'category" column that I want to
maintain in a separate table.
CREATE TABLE products (
product_id INTEGER DEFAULT
nextval('product_id_seq'::regclass) NOT NULL,
name VARCHAR(60) NOT NULL,
category SMALLINT NOT NULL,
CONSTRAINT product_id PRIMARY KEY (product_id)
);
CREATE TABLE products (
category_id INTEGER DEFAULT
nextval('category_id_seq'::regclass) NOT NULL,
name VARCHAR(20) NOT NULL,
CONSTRAINT category_id PRIMARY KEY (category_id)
);
Every product must have a category,
Since many (but not all) products have the same category I only want 1
table with unique categories.
To do the insert into the products table I need to retrieve or insert
the category_id in categories first.
Which means more code on my client app (if ($cat_id =
get_cat_id($cat)) }else { $cat_id = insert_cat($cat)})
Can I write a BEFORE ROW trigger for the products table to runs on
INSERT or UPDATE to
1. insert a new category & return the new category_id OR
2. return the existing category_id for the (to be inserted row)
Alan
I donproducts.category to be a foreign key that points to the uniqie
category_id id in the want to keep I need to do get the cate
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: insert
2011-07-23 16:23 insert alan <alan.miller3@gmail.com>
@ 2011-07-25 14:34 ` alan <alan.miller3@gmail.com>
2011-08-01 08:52 ` Re: insert Vitalii Tymchyshyn <tivv00@gmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: alan @ 2011-07-25 14:34 UTC (permalink / raw)
To: pgsql-performance
I think I figured it out myself.
If anyone sees issues with this (simple) approach, please let me know.
I changed my table definitions to this:
CREATE SEQUENCE public.product_id_seq
CREATE TABLE products (
product_id INTEGER DEFAULT nextval('product_id_seq'::regclass) NOT
NULL,
name VARCHAR(60) NOT NULL,
category SMALLINT NOT NULL,
CONSTRAINT product_id PRIMARY KEY (product_id)
);
CREATE SEQUENCE public.category_id_seq
CREATE TABLE category (
category_id INTEGER DEFAULT nextval('category_id_seq'::regclass)
NOT NULL,
name VARCHAR(20) NOT NULL,
CONSTRAINT category_id PRIMARY KEY (category_id)
);
ALTER TABLE products ADD CONSTRAINT category_products_fk
FOREIGN KEY (category)
REFERENCES category (category_id)
ON DELETE NO ACTION ON UPDATE CASCADE
;
Then created this function:
CREATE OR REPLACE FUNCTION getid(_table text,_pk text,_name text)
RETURNS integer AS $$
DECLARE _id integer;
BEGIN
EXECUTE 'SELECT '
|| _pk
|| ' FROM '
|| _table::regclass
|| ' WHERE name'
|| ' = '
|| quote_literal(_name)
INTO _id;
IF _id > 0 THEN
return _id;
ELSE
EXECUTE 'INSERT INTO '
|| _table
|| ' VALUES (DEFAULT,' || quote_literal(_name) || ')'
|| ' RETURNING ' || _pk
INTO _id;
return _id;
END IF;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
Now I can just insert into the products table via:
INSERT INTO products VALUES(DEFAULT,'Postgresql for
Dummies',getid('category','category_id','books'));
For example:
testdb=# select * from products;
product_id | name | category
------------+------+----------
(0 rows)
iims_test=# select * from category;
category_id | name
-------------+------
(0 rows)
testdb=# insert into products values(DEFAULT,'Postgresql for
Dummies',getid('category','category_id','books'));
INSERT 0 1
testdb=# select * from
category;
category_id | name
-------------+-------
1 | books
testdb=# select * from products;
product_id | name | category
------------+------------------------+----------
1 | Postgresql for Dummies | 1
Updating the category_id in category table are also cascaded to the
product table.
testdb=# UPDATE category SET category_id = 2 WHERE category_id = 1;
UPDATE 1
testdb=# SELECT * FROM products;
product_id | name | category
------------+------------------------+----------
1 | Postgresql for Dummies | 2
Alan
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: insert
2011-07-23 16:23 insert alan <alan.miller3@gmail.com>
2011-07-25 14:34 ` Re: insert alan <alan.miller3@gmail.com>
@ 2011-08-01 08:52 ` Vitalii Tymchyshyn <tivv00@gmail.com>
2011-08-01 14:47 ` Re: insert Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 19+ messages in thread
From: Vitalii Tymchyshyn @ 2011-08-01 08:52 UTC (permalink / raw)
To: alan <alan.miller3@gmail.com>; +Cc: pgsql-performance
Hello.
Please note that in multitasking environment you may have problems with
your code. Two connections may check if "a" is available and if not (and
both got empty "select" result), try to insert. One will succeed,
another will fail if you have a unique constraint on category name (and
you'd better have one).
Please note that select for update won't help you much, since this is
new record you are looking for, and select don't return (and lock) it. I
am using "lock table <tableName> in SHARE ROW EXCLUSIVE mode" in this case.
But then, if you have multiple lookup dictinaries, you need to ensure
strict order of locking or you will be getting deadlocks. As for me, I
did create a special application-side class to retrieve such values. If
I can't find a value in main connection with simple select, I open new
connection, perform table lock, check if value is in there. If it is
not, add the value and commit. This may produce orphaned dictionary
entries (if dictionary entry is committed an main transaction is rolled
back), but this is usually OK for dictionaries. At the same time I don't
introduce hard locks into main transaction and don't have to worry about
deadlocks.
Best regards, Vitalii Tymchyshyn
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: insert
2011-07-23 16:23 insert alan <alan.miller3@gmail.com>
2011-07-25 14:34 ` Re: insert alan <alan.miller3@gmail.com>
2011-08-01 08:52 ` Re: insert Vitalii Tymchyshyn <tivv00@gmail.com>
@ 2011-08-01 14:47 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 19+ messages in thread
From: Kevin Grittner @ 2011-08-01 14:47 UTC (permalink / raw)
To: alan <alan.miller3@gmail.com>; Vitalii Tymchyshyn <tivv00@gmail.com>; +Cc: pgsql-performance
Vitalii Tymchyshyn <tivv00@gmail.com> wrote:
> Please note that in multitasking environment you may have problems
> with your code. Two connections may check if "a" is available and
> if not (and both got empty "select" result), try to insert. One
> will succeed, another will fail if you have a unique constraint on
> category name (and you'd better have one).
>
> Please note that select for update won't help you much, since this
> is new record you are looking for, and select don't return (and
> lock) it. I am using "lock table <tableName> in SHARE ROW
> EXCLUSIVE mode" in this case.
>
> But then, if you have multiple lookup dictinaries, you need to
> ensure strict order of locking or you will be getting deadlocks.
> As for me, I did create a special application-side class to
> retrieve such values. If I can't find a value in main connection
> with simple select, I open new connection, perform table lock,
> check if value is in there. If it is not, add the value and
> commit. This may produce orphaned dictionary entries (if
> dictionary entry is committed an main transaction is rolled back),
> but this is usually OK for dictionaries. At the same time I don't
> introduce hard locks into main transaction and don't have to worry
> about deadlocks.
It sounds like you might want to check out the new "truly
serializable" transactions in version 9.1. If you can download the
latest beta version of it and test with
default_transaction_isolation = 'serializable' I would be interested
to hear your results. Note that you can't have deadlocks, but you
can have other types of serialization failures, so your software
needs to be prepared to start a transaction over from the beginning
when the SQLSTATE of a failure is '40001'.
The Wiki page which was used to document and organize the work is:
http://wiki.postgresql.org/wiki/Serializable
This is in a little bit of a funny state because not all of the
wording that was appropriate while the feature was under development
(e.g., future tense verbs) has been changed to something more
appropriate for a finished feature, but it should cover the
theoretical ground pretty well. An overlapping document which was
initially based on parts of the Wiki page and has received more
recent attention is the README-SSI file here:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob_plain;f=src/backend/storage/lmgr/README-SS...
Some examples geared toward programmers and DBAs is at this Wiki
page:
http://wiki.postgresql.org/wiki/SSI
It could use a couple more examples and a bit of language cleanup,
but what is there is fairly sound. The largest omission is that we
need to show more explicitly that serialization failures can occur
at times other than COMMIT. (I got a little carried away trying to
show that there was no blocking and that the "first committer
wins".)
-Kevin
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: insert
2011-07-23 16:23 insert alan <alan.miller3@gmail.com>
@ 2011-07-29 20:14 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-07-30 05:24 ` Re: insert Gavin Flower <GavinFlower@archidevsys.co.nz>
1 sibling, 1 reply; 19+ messages in thread
From: Kevin Grittner @ 2011-07-29 20:14 UTC (permalink / raw)
To: alan <alan.miller3@gmail.com>; pgsql-performance
alan <alan.miller3@gmail.com> wrote:
> Can I write a BEFORE ROW trigger for the products table to runs
> on INSERT or UPDATE to
> 1. insert a new category & return the new category_id OR
> 2. return the existing category_id for the (to be inserted row)
What would you be using to match an existing category? If this
accurately identifies a category, why not use it for the key to the
category table, rather than generating a synthetic key value?
-Kevin
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: insert
2011-07-23 16:23 insert alan <alan.miller3@gmail.com>
2011-07-29 20:14 ` Re: insert Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2011-07-30 05:24 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
0 siblings, 0 replies; 19+ messages in thread
From: Gavin Flower @ 2011-07-30 05:24 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: alan <alan.miller3@gmail.com>; pgsql-performance
On 30/07/11 08:14, Kevin Grittner wrote:
> alan<alan.miller3@gmail.com> wrote:
>
>> Can I write a BEFORE ROW trigger for the products table to runs
>> on INSERT or UPDATE to
>> 1. insert a new category& return the new category_id OR
>> 2. return the existing category_id for the (to be inserted row)
>
> What would you be using to match an existing category? If this
> accurately identifies a category, why not use it for the key to the
> category table, rather than generating a synthetic key value?
>
> -Kevin
>
Hi Alan,
This is the way I would define the tables, I think it conforms tom your
requirements, and the definitions look clearer.
I have the convention that the id of the table itself is not prefixed
with the table name, but references to the id field of other tables are
(e.g. category_id). This is not something you need to follow, but it
helps to clearly identify what is a foreign key, and what is the current
table's id! Likewise, I think it is simpler to make the table names
singular, but this again is a bit arbitrary.
I guess, even if you prefer my conventions, it is more important to
follow the standards of the existing database!
CREATE TABLE product
(
id SERIAL PRIMARY KEY,
category_id int REFERENCES category(id),
name VARCHAR(60) NOT NULL
);
CREATE TABLE category
(
id SERIAL PRIMARY KEY,
name VARCHAR(20) UNIQUE NOT NULL
);
Though for the primary key of the category table, it might be better to
explicitly assign the key, then you have more control of the numbers used.
I would be a bit wary of automatically inserting a new category, when
the given category is not already there, you could end up with several
variations of spelling for the same category! I once saw a system with
about 20 variations of spelling, and number of spaces between words, for
the name of the same company!
Possibly your client GUI application could have a drop down list of
available categories, and provision to enter new ones, but then this
might be outside your control.
Cheers,
GAvin
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2011-08-01 14:47 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-13 07:47 insert Ulrich Wisser <ulrich.wisser@relevanttraffic.se>
2004-08-13 10:08 Re: insert G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
2004-08-13 10:49 ` gnari <gnari@simnet.is>
2004-08-14 14:24 ` Gaetano Mendola <mendola@bigfoot.com>
2004-08-13 11:16 Re: insert G u i d o B a r o s i o <gbarosio@uolsinectis.com.ar>
2004-08-13 11:47 ` gnari <gnari@simnet.is>
2004-08-13 12:10 ` Ulrich Wisser <ulrich.wisser@relevanttraffic.se>
2004-08-13 12:41 ` Paul Thomas <paul@tmsl.demon.co.uk>
2004-08-13 12:57 ` Rod Taylor <pg@rbt.ca>
2004-08-13 15:02 ` Bruno Wolff III <bruno@wolff.to>
2004-08-13 16:17 ` Matt Clark <matt@ymogen.net>
2004-08-13 16:31 ` Bruno Wolff III <bruno@wolff.to>
2004-08-13 18:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-07-23 16:23 insert alan <alan.miller3@gmail.com>
2011-07-25 14:34 ` alan <alan.miller3@gmail.com>
2011-08-01 08:52 ` Vitalii Tymchyshyn <tivv00@gmail.com>
2011-08-01 14:47 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-07-29 20:14 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-07-30 05:24 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox