Re: [GENERAL] isn't "insert into where not exists" atomic?
On 02/04/2011 06:57 AM, pasman pasmański wrote: Mage, add "raise notice" at the begin of your buggy trigger. There is a little bit of difference between "Your trigger is wrong. You try to insert the same row twice" and "The trigger will be fired twice." You stated the first but the second is the truth. Nevermind, my solution will be using table level lock for this case. Mage -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] isn't "insert into where not exists" atomic?
On 02/03/2011 08:23 PM, Tom Lane wrote: Mage writes: The main question is that isn't "insert into ... select ... where not exists" atomic? No, it isn't: it *will* fail in the presence of other transactions doing the same thing, because the EXISTS test will only see rows that committed before the command started. You might care to read the manual's chapter about concurrency: http://www.postgresql.org/docs/9.0/static/mvcc.html Thank you, Tom. I will read that. However I googled a bit before written this trigger and I would like to ask you: what is the best practice for doing "insert or update"-like thing, especially in this case, in trigger? I would use lock table from now. Is it the recommended way? (I just don't like the "insert -> on exception -> update" method). Mage -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] isn't "insert into where not exists" atomic?
On 02/03/2011 10:35 PM, pasman pasmański wrote:
Your trigger is wrong. You try to insert the same row twice.
I assume you didn't try it. If active_at field is null then the trigger
does another insert instead of the original one. This avoids looping or
inserting twice.
The only mistake is that I tought the insert with select will be atomic
and I was wrong. So the trigger has concurrency problem in
multi-threaded environment. It runs flawlessly in single thread and it
does only a single insert.
Mage
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] isn't "insert into where not exists" atomic?
On 02/03/2011 08:13 AM, Alban Hertroys wrote: On 3 Feb 2011, at 2:17, Mage wrote: The trigger looks like: create or replace function trf_chat_room_users_insert() returns trigger as $$ begin if NEW.active_at is null then insert into chat_room_users (user_id, chat_room_id, active_at) (select NEW.user_id, NEW.chat_room_id, now() where not exists (select 1 from chat_room_users where user_id = NEW.user_id and chat_room_id = NEW.chat_room_id)); if not found then update chat_room_users set active_at = now() where user_id = NEW.user_id and chat_room_id = NEW.chat_room_id; end if; return null; end if; return NEW; end; $$ language plpgsql; Your trigger is the wrong way around. Insert doesn't set found, but update does. Alban Hertroys I think you missed the point that the insert contains a select which sets found. My trigger works fine and it was called thousands times. It just dropped an exception two times. The main question is that isn't "insert into ... select ... where not exists" atomic? Anyway, it you'd try it: create table chat_room_users ( user_id int not null, chat_room_id int not null, active_at timestamp with time zone not null ); create unique index chu_user_id_chat_room_id on chat_room_users (user_id, chat_room_id); create or replace function trf_chat_room_users_insert() returns trigger as $$ begin if NEW.active_at is null then insert into chat_room_users (user_id, chat_room_id, active_at) (select NEW.user_id, NEW.chat_room_id, now() where not exists (select 1 from chat_room_users where user_id = NEW.user_id and chat_room_id = NEW.chat_room_id)); if not found then update chat_room_users set active_at = now() where user_id = NEW.user_id and chat_room_id = NEW.chat_room_id; end if; return null; end if; return NEW; end; $$ language plpgsql; create trigger tr_chat_room_users_insert before insert on chat_room_users for each row execute procedure trf_chat_room_users_insert(); insert into chat_room_users (user_id, chat_room_id) values (1, 1); insert into chat_room_users (user_id, chat_room_id) values (2, 1); insert into chat_room_users (user_id, chat_room_id) values (1, 1); insert into chat_room_users (user_id, chat_room_id) values (2, 1); Mage -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] isn't "insert into where not exists" atomic?
Hello,
I just received an error message:
PGError: ERROR: duplicate key value violates unique constraint
"chu_user_id_chat_room_id"
DETAIL: Key (user_id, chat_room_id)=(8, 2) already exists.
CONTEXT: SQL statement "insert into chat_room_users (user_id, chat_room_id,
active_at) (select NEW.user_id, NEW.chat_room_id, now() where not exists (select 1 from
chat_room_users where user_id = NEW.user_id and chat_room_id = NEW.chat_room_id))"
PL/pgSQL function "trf_chat_room_users_insert" line 3 at SQL statement
: INSERT INTO "chat_room_users" ("user_id", "chat_room_id", "active_at") VALUES
(8, 2, NULL)
The important line is:
insert into chat_room_users (user_id, chat_room_id, active_at) (select
NEW.user_id, NEW.chat_room_id, now() where not exists (select 1 from
chat_room_users where user_id = NEW.user_id and chat_room_id =
NEW.chat_room_id))
I always thought this is atomic and can not fail. Was I wrong?
If it isn't then I have to rewrite my triggers. Do I have to use "lock
table" instead of the above to avoid errors in parallel inserts?
The trigger looks like:
create or replace function trf_chat_room_users_insert() returns trigger
as $$
begin
if NEW.active_at is null then
insert into chat_room_users (user_id, chat_room_id,
active_at) (select NEW.user_id, NEW.chat_room_id, now() where not exists
(select 1 from chat_room_users where user_id = NEW.user_id and
chat_room_id = NEW.chat_room_id));
if not found then
update chat_room_users set active_at = now()
where user_id = NEW.user_id and chat_room_id = NEW.chat_room_id;
end if;
return null;
end if;
return NEW;
end;
$$ language plpgsql;
And it meant to be "insert or update".
Mage
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] subquery join order by
I considered this, however the subquery is generated by an ORM. I
wanted to separate it.
Also the whole join affects many rows. I thought it's cheaper to
preselect them inside the subquery then do the join. I am not sure.
Explain analyze is my good friend but in this case I prefer to ask.
# EXPLAIN ANALYZE select * from (select distinct on (b_id) * from a
order by b_id, id) sub left join b on b.id = sub.b_id;
QUERY PLAN
-
Hash Left Join (cost=187.45..243.70 rows=1230 width=44) (actual
time=0.000..0.000 rows=3 loops=1)
[...]
(11 rows)
# EXPLAIN ANALYZE SELECT DISTINCT ON (a.b_id) * FROM a LEFT JOIN b ON
b.id = a.b_id ORDER BY a.b_id, a.id;
QUERY PLAN
Unique (cost=1339.24..1405.05 rows=200 width=44) (actual
time=0.000..0.000 rows=3 loops=1)
[...]
(15 rows)
mage=# EXPLAIN ANALYZE select * from (select distinct on (b_id) * from a
order by b_id, id) sub left join b on b.id = sub.b_id order by b.id;
QUERY PLAN
---
Sort (cost=306.83..309.90 rows=1230 width=44) (actual
time=0.000..0.000 rows=3 loops=1)
The subquery seems to be better choice even with double ordering. But is
the second order required?
Mage
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] subquery join order by
On 11/19/2010 03:21 AM, Thom Brown wrote: You should always use ORDER BY on the outer-most part of the query since that's what will be finally returning your data. Don't bother with ordering sub-selects. I definiatelly have to use the "order by" inside for two reasons. When "distinct on (x)" is used then x must be in the first column in the order by part. The second column in the order by decides which records will I include in the join so it is very important to use it for ordering. So in your case, just use: SELECT * FROM (SELECT DISTINCT ON (b_id) * FROM a) sub LEFT JOIN b ON b.id = sub.b_id ORDER BY sub.b_id, sub.id; select distinct on (id) * from b order by name; ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions But why bother with a sub-select anyway? You can write it as: SELECT DISTINCT ON (a.b_id) * FROM a LEFT JOIN b ON b.id = a.b_id ORDER BY a.b_id, a.id; I considered this, however the subquery is generated by an ORM. I wanted to separate it. Also the whole join affects many rows. I thought it's cheaper to preselect them inside the subquery then do the join. I am not sure. Explain analyze is my good friend but in this case I prefer to ask. Mage -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] subquery join order by
Hello, (I googled and read docs before sending this e-mail). Is it necessary to use order by twice (inside and outside) to get the proper order if I have an ordered subqery in a join? select * from (select distinct on (b_id) * from a order by b_id, id) sub left join b on b.id = sub.b_id; or select * from (select distinct on (b_id) * from a order by b_id, id) sub left join b on b.id = sub.b_id order by b_id; It seems to me that it's enough to use 'order by' only inside wheter 'by desc' or 'by asc' (b_id), however I'd like to be sure. Thank you. Mage -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] upgrading to 8.3, utf-8 and latin2 locale problem
Tom Lane wrote: Mage <[EMAIL PROTECTED]> writes: We would like to upgrade from 8.1 to 8.3. We have UTF-8 and LATIN2 databases. Any idea? If you were running with a non-C database locale, that was always broken in 8.1, and you are very fortunate not to have stumbled across any of the failure cases. You can either standardize on UTF8 for all your databases (note that this does not stop your *clients* from using LATIN2 if they want), or use C locale which will work equally poorly with all encodings ;-) If it were up to me, I'd never use LATIN2. I switched to unicode years ago. Some of our databases don't belong to me and I can't modify their clients. What is the proper use of "create database encoding = 'yyy'" in postgresql 8.3? If I understand You, I should avoid it totally, and convert every affected database dumps to UTF-8, load them and use "alter database xxx set client_encoding = 'latin2'". Is it right? Mage -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] upgrading to 8.3, utf-8 and latin2 locale problem
Hello, I am sure this won't be the first e-mail about this issue, however we are upgrading production-like environment. Please help. For reproducing I've used two debian servers, same locales (en_US.UTF-8, en_US ISO-8859-1, hu_HU.UTF-8, hu_HU ISO-8859-2), Debian testing. Postgresql 8.2 (8.2.6-2): /usr/lib/postgresql/8.2/bin/initdb -D /home/readonly/pg_data/ --locale='en_US.UTF-8' --lc-collate='hu_HU.UTF-8' --lc-ctype='hu_HU.UTF-8' --lc-time='hu_HU.UTF-8' The files belonging to this database system will be owned by user "mage". This user must also own the server process. The database cluster will be initialized with locales COLLATE: hu_HU.UTF-8 CTYPE:hu_HU.UTF-8 MESSAGES: en_US.UTF-8 MONETARY: en_US.UTF-8 NUMERIC: en_US.UTF-8 TIME: hu_HU.UTF-8 The default database encoding has accordingly been set to UTF8. /usr/lib/postgresql/8.2/bin/pg_ctl -D /home/readonly/pg_data -l logfile -o '-p ' start /usr/lib/postgresql/8.2/bin/psql -p template1 # create database test encoding = 'latin2'; CREATE DATABASE Postgresql 8.3 (8.3.0-1): /usr/lib/postgresql/8.3/bin/initdb -D /home/readonly/pg_data/ --locale='en_US.UTF-8' --lc-collate='hu_HU.UTF-8' --lc-ctype='hu_HU.UTF-8' --lc-time='hu_HU.UTF-8' The files belonging to this database system will be owned by user "mage". This user must also own the server process. The database cluster will be initialized with locales COLLATE: hu_HU.UTF-8 CTYPE:hu_HU.UTF-8 MESSAGES: en_US.UTF-8 MONETARY: en_US.UTF-8 NUMERIC: en_US.UTF-8 TIME: hu_HU.UTF-8 The default database encoding has accordingly been set to UTF8. The default text search configuration will be set to "hungarian". /usr/lib/postgresql/8.3/bin/pg_ctl -D /home/readonly/pg_data -l logfile -o '-p ' start /usr/lib/postgresql/8.3/bin/psql -p template1 template1=# create database test encoding = 'latin2'; ERROR: encoding LATIN2 does not match server's locale hu_HU.UTF-8 DETAIL: The server's LC_CTYPE setting requires encoding UTF8. In Google we've found similar err messages for pg_upgradecluster. Both server: show all; client_encoding | UTF8 lc_collate | hu_HU.UTF-8 lc_ctype| hu_HU.UTF-8 lc_messages | en_US.UTF-8 lc_monetary | en_US.UTF-8 lc_numeric | en_US.UTF-8 lc_time | hu_HU.UTF-8 server_encoding | UTF8 We would like to upgrade from 8.1 to 8.3. We have UTF-8 and LATIN2 databases. Any idea? Mage -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] partial word matching
Hello, as far as I know treach2 doesn't support partial word matching. Last time I checked it didn't. (I also googled before I asking this). Am I wrong? If not, we would implement some FTI, something like the old and decrepated FTI-crontib. Where should we start reading tutorials? I googled for this question too, however most results were about tsearch2 or old FTI contrib. I would read something advanced about this topic. Something that must be read. Thank you. (Before asking I was searching in my last 30k e-mails from this mailing list. Sadly they were not full-text indexed.) Mage ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] plruby
Hello, is there any planned date when plruby will be officially included to postgresql source (and documentation)? Mage ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] is this a bug or I am blind?
Martijn van Oosterhout wrote: On Sat, Dec 17, 2005 at 05:01:15PM -0500, Tom Lane wrote: Martijn van Oosterhout writes: I think the real solution is to implement COLLATE support. Maybe so, but we still need to figure out what we're doing for the back branches, and that won't be it ... To be honest, there are really only a handful of locales that suffer from this issue, so perhaps we should document it and move on. I don't agree. Usually I read the whole documentation of the software I use, but you cannot presume that every user even with good sql skills will check the documentation for a thing he wouldn't imagine. With knowing the background it is understandable locale problem, but in the user's point of view it's a weird and serious bug which shouldn't be there. Using hu_HU with latin2 is a normal marrying. Some users (including me) don't always read the "known issues" chapter, even for a good quality software. Mage ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] is this a bug or I am blind?
Tom Lane wrote: Bruce Momjian writes: Where are we on this? Waiting to see if there's any input on what the behavior needs to be. Actually, "potyty" and "potty" are not equal in Hungarian language. We use the "tyty" form in complex words. Only data sorting requires the knowledge that they are neighbours. I am very afraid of queries when a condition has different meanings in the select and in the where clause. It is the worst. Even if the "tyty" and "tty" were same, I couldn't accept the actual behavior of the indexes, but they are not same. Mage ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] is this a bug or I am blind?
UPDATE I was trying to create a demo table, because I cannot send our confidental data. I have found weird result. # drop table common_logins; DROP TABLE $ psql < ../cl.sql SET SET SET SET SET SET CREATE TABLE setval 203650 (1 row) ALTER TABLE CREATE INDEX CREATE INDEX # select * from common_logins where username = 'potyty'; uid | username | password | lastlogin | status | usertype | loginnum -+--+--+---++--+-- (0 rows) # VACUUM FULL analyze; VACUUM # select * from common_logins where username = 'potyty'; uid | username | password | lastlogin | status | usertype | loginnum -+--+--+---++--+-- (0 rows) # select count(1) from common_logins; count 203361 (1 row) # delete from common_logins where uid in (select uid from common_logins where username not ilike 'potyty' limit 10); DELETE 10 mage=# select * from common_logins where username = 'potyty'; uid | username | password | lastlogin | status | usertype | loginnum -+--+--+---++--+-- (0 rows) # VACUUM FULL analyze; VACUUM # select * from common_logins where username = 'potyty'; uid | username | password | lastlogin | status | usertype | loginnum +--+--+++--+-- 155505 | potyty | board| 2004-08-16 17:45:55.723829 | A | S|1 60067 | potyty | board| 2004-07-07 20:22:17.68699 | A | S|3 (2 rows) # delete from common_logins where uid in (select uid from common_logins where username not ilike 'potyty' limit 8); DELETE 8 # VACUUM FULL analyze; VACUUM # select * from common_logins where username = 'potyty'; uid | username | password | lastlogin | status | usertype | loginnum +--+--+++--+-- 174041 | potyty | board| 2005-02-17 00:00:13.706144 | A | S|3 60067 | potyty | board| 2004-07-07 20:22:17.68699 | A | S|3 155505 | potyty | board| 2004-08-16 17:45:55.723829 | A | S|1 (3 rows) The 2 rows part seems to be (for me) a non-locale-related, but serious problem. I have the data file, it is confidental, but I can send it to official pg developers if needed. Mage Tom Lane wrote: Jaime Casanova <[EMAIL PROTECTED]> writes: On 12/15/05, Csaba Nagy <[EMAIL PROTECTED]> wrote: Ok, that explains then the problem... but the index is arguably corrupt in this case, with the important difference that it's not even fixable by reindex... I guess what the OP really wants is a solution to his problem. MAGE was reproducing the problem in a little table that can be send but now tolds me that the problem in the test table disappear when a VACUUM was executed... is this consistent with the idea of locale problem? The VACUUM might have caused the planner not to use the index anymore; check EXPLAIN. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] is this a bug or I am blind?
Mage wrote: ./configure --prefix=/usr/local/pgsql --with-python client_encoding| LATIN2 lc_collate | hu_HU lc_ctype | hu_HU lc_messages| en_US lc_monetary| en_US lc_numeric | en_US lc_time| en_US server_encoding| LATIN2 server_version | 8.0.3 I have created a table that can be sent to you to examine the bug. I am actually vacuuming it for further testing. Where can I upload it? Sorry, I cannot host it. Mage ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] is this a bug or I am blind?
Jaime Casanova wrote:
I tried it in two databases (dump and load to another one), so I don't
think that we have corrupted indexes.
the problem persist after a dump a reload? then there is not because
an index corruption... can you send part of the data that reproduces
the bug?
I can try on a newer version of postgresql on another server.
By the way, if this is a bug then it's a serious one. We have it in
production environment.
Mage
what locales do you have? encoding?
The problem can be reproduced on pgsql 8.0.3, compiled from source.
This is a third machine where the bug persists. I dumped the table and
loaded in.
I cannot send the table to you because it contains user data and
passwords. I will try to create a fake one with the same problem.
./configure --prefix=/usr/local/pgsql --with-python
client_encoding| LATIN2
lc_collate | hu_HU
lc_ctype | hu_HU
lc_messages| en_US
lc_monetary| en_US
lc_numeric | en_US
lc_time| en_US
server_encoding| LATIN2
server_version | 8.0.3
Mage
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] is this a bug or I am blind?
Csaba Nagy wrote: [snip] even is that is true i think you need the "comodin characters" (ie: %. _) to make "like" behave different from simple comparisons Not entirely true, if the database was initialized in a different locale than C, then the direct comparison will probably go for an index on username, while "like" will not. Which points to a possible index corruption... which might be interesting for the developers to investigate, but I would guess a reindex will solve the problem for the OP if he has it urgent... I thought that it may be a locale problem, but: - look at my 3rd query - potyty doesn't contain special chars # EXPLAIN ANALYZE select * from common_logins where username = 'potyty'; QUERY PLAN --- Index Scan using common_logins_username_idx on common_logins (cost=0.00..4.30 rows=1 width=47) (actual time=0.056..0.056 rows=0 loops=1) Index Cond: ((username)::text = 'potyty'::text) Total runtime: 0.109 ms (3 rows) online=# EXPLAIN ANALYZE select * from common_logins where username like 'potyty'; QUERY PLAN Seq Scan on common_logins (cost=0.00..63833.88 rows=1 width=47) (actual time=180.333..262.492 rows=3 loops=1) Filter: ((username)::text ~~ 'potyty'::text) Total runtime: 262.551 ms (3 rows) I tried it in two databases (dump and load to another one), so I don't think that we have corrupted indexes. I can try on a newer version of postgresql on another server. By the way, if this is a bug then it's a serious one. We have it in production environment. Mage ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] is this a bug or I am blind?
A. Kretschmer wrote:
select *, length(username), length('potyty') from common_logins where username
like 'potyty';
My guess:
select length(username) from common_logins where username like 'potyty';
is _NOT_ 6, there is a SPACE like 'potyty '.
If you look my 3rd query, you will see that there are no spaces, however:
select *, length(username), length('potyty') from common_logins where
username like 'potyty';
uid | username | password | lastlogin | status |
usertype | loginnum | length | length
+--+--+++--+--++
155505 | potyty | board| 2004-08-16 17:45:55.723829 | A |
S|1 | 6 | 6
60067 | potyty | board| 2004-07-07 20:22:17.68699 | A |
S|3 | 6 | 6
174041 | potyty | board| 2005-02-17 00:00:13.706144 | A |
S|3 | 6 | 6
(3 rows)
Mage
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] is this a bug or I am blind?
online=# select * from common_logins where username = 'potyty'; uid | username | password | lastlogin | status | usertype | loginnum -+--+--+---++--+-- (0 rows) online=# select * from common_logins where username like 'potyty'; uid | username | password | lastlogin | status | usertype | loginnum +--+--+++--+-- 155505 | potyty | board| 2004-08-16 17:45:55.723829 | A | S|1 60067 | potyty | board| 2004-07-07 20:22:17.68699 | A | S|3 174041 | potyty | board| 2005-02-17 00:00:13.706144 | A | S|3 (3 rows) online=# select username, username = 'potyty' from common_logins where username like 'potyty'; username | ?column? --+-- potyty | t potyty | t potyty | t (3 rows) psql 8.0.3, Debian. Mage ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] atomic function
Dennis Bjorklund wrote: On Mon, 15 Aug 2005, Mage wrote: 2005-08-12 19:08:43: ERROR: duplicate key violates unique constraint "common_adviewnum_adid_site_day_index" Between your select and your insert someone else inserted a row making the insert fail. I see. I thought that the function will be atomic but I was wrong. See this example of how you can update or insert depending on what is in the table: http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE Or should I simply lock the table? Mage ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] atomic function
Hello! What's wrong with this function? public | common_adviewnum_increase | integer | bigint, character varying | postgres | plpgsql | declare row record; result int; begin select into row viewnum from common_adviewnum where adid = $1 and site = $2 and day = now()::date; if found then result = row.viewnum + 1; update common_adviewnum set viewnum = result where adid = $1 and site = $2 and day = now()::date; else result = 1; insert into common_adviewnum (adid, site, day, viewnum) values ($1, $2, now()::date, result); end if; return result; end; Every 2-3 day I get this in the server log: 2005-08-12 19:08:43: ERROR: duplicate key violates unique constraint "common_adviewnum_adid_site_day_index" CONTEXT: SQL statement "insert into common_adviewnum (adid, site, day, viewnum) values ( $1 , $2 , now()::date, $3 )" PL/pgSQL function "common_adviewnum_increase" line 11 at SQL statement select common_adviewnum_increase(820434,'H'); Mage ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] plpython setof row
Hello, how can I return setof rows in plpython language? I read the manual and couldn't find. Mage ---(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: [GENERAL] can I send execution log into a file.
Dinesh Pandey wrote: > I am using Postgres. How can I send execution log into a file. > > > psql -o filename I also recommend: psql -H -e -o filename Mage ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] plpython bug
Update:
it might be not plpython, but similar to the plperl bug I found last time.
The script which can produce the bug:
create table test (id int, date timestamp);
create or replace function trigger_test() returns trigger as $$
plpy.info(TD['new'])
return 'MODIFY'
$$ language plpythonu;
create trigger test_update before update on test for each row execute
procedure trigger_test();
insert into test values (1, now());
insert into test values (2, now());
update test set id = 3;
create or replace function test_perl() returns boolean as $$
use locale;
use POSIX qw(locale_h);
setlocale(LC_COLLATE,'hu_HU');
setlocale(LC_CTYPE,'hu_HU');
setlocale(LC_NUMERIC,'hu_HU');
return True
$$ language plperlu;
create or replace function trigger_test() returns trigger as $$
plpy.info(TD['new'])
plpy.execute('select * from test_perl()')
return 'MODIFY'
$$ language plpythonu;
update test set id = 4;
-
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
INSERT 9138862 1
INSERT 9138863 1
INFO: ({'date': '2005-05-05 13:20:43.793551', 'id': 3},)
INFO: ({'date': '2005-05-05 13:20:43.794401', 'id': 3},)
UPDATE 2
CREATE FUNCTION
CREATE FUNCTION
INFO: ({'date': '2005-05-05 13:20:43.793551', 'id': 4},)
ERROR: invalid input syntax for type timestamp: "2005-05-05
13:20:43.793551"
---
I don't think that plperl or plperlu with locales should be used in
production environment.
Mage
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[GENERAL] plpython bug
Hello! create or replace function trigger_keywords_maintain() returns trigger as $$ return 'MODIFY' $$ language plpythonu; update table set id = id where id = 7 ERROR: invalid input syntax for type timestamp: "2005-05-03 14:07:33,279213" I see that Python's timestamp format is not accepted by postgresql. Mage ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] lower function
Scott Marlowe wrote: > >You're far more likely to learn tcl or python or php in an afternoon >than to get a patched perl executable in that time. > >But I'd still report the bug to them. > > create or replace function keywords_split(text) returns text as $$ use locale; use POSIX qw(locale_h); setlocale(LC_CTYPE,'hu_HU'); return ''; $$ language plperlu; It works. And it's so nasty. I have to insert these into every plperl function. I am not subscribed to any perl list. Would someone report this bug? Mage ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] lower function
Tom Lane wrote: >Mage <[EMAIL PROTECTED]> writes: > > >>It's serious. >> >> > >That's a Perl bug not a Postgres bug: libperl should not change the >process's locale settings, or at least if it does it should restore >the prior settings before returning. It doesn't. > > > I checked with show all, client and server encoding remained latin2, and lc_ctype remained hu_HU. However, the lower function got corrupted (encoding) until the end of the session. I can reproduce the bug on an Debian Sarge and on a Gentoo. Both are up-to-date. What should I do? Subscribe to perl list and tell about this? I have to write a trigger which can't be written well in plpgsql. My options are to learn python or tcl on basic level in one day. I am not sure I want and can do this. Mage ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] lower function
It's serious.
teszt=# select lower('AúéöÖÉÁ');
lower
-
aúéööéá
(1 row)
teszt=# create or replace function keywords_split(text) returns text as $$
teszt$# return '';
teszt$# $$
teszt-# language plperlu;
CREATE FUNCTION
teszt=# select keywords_split('');
keywords_split
(1 row)
teszt=# select lower('AúéöÖÉÁ');
lower
-
aúéöÖÉÁ
(1 row)
Mage
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] lower function
Daniel Verite wrote:
> Mage wrote:
>
>
>
>>with use locale;:
>>
>>select keywords_split('AúéöÖÉÁ');
>>ERROR: creation of Perl function failed: 'require' trapped by operation
>>mask at (eval 6) line 2.
>>
>>
>
>Ah. So maybe it would work with plperlu instead of plperl.
>
>
I did, and it didn't help.
Mage
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] lower function
Daniel Verite wrote:
> Mage wrote:
>
>
>
>>teszt=# select keywords_split('AúéöÖÉÁ');
>> keywords_split
>>
>> aúéöÖÉÁ
>>(1 row)
>>
>>
>
>What happens if you add
> use locale;
>in your perl function before calling lc ?
>
>
with use locale;:
select keywords_split('AúéöÖÉÁ');
ERROR: creation of Perl function failed: 'require' trapped by operation
mask at (eval 6) line 2.
Another problem:
create or replace function keywords_split(text) returns text as $$
my $res = spi_exec_query("select lower('" . $_[0] . "')");
my $text = 'test' . $res->{rows}[0]->{lower};
return $text;
$$
language plperl;
# select keywords_split('AúéöÖÉÁ');
keywords_split
testaúéöÖÉÁ
(1 row)
The spi_exec_query with lower also don't work.
I have found another bug in a plperl trigger which I can't reproduce. I
find plperl a bit buggy.
Mage
---(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
[GENERAL] lower function
Hello,
I have a database with encoding latin2, ctype hu_HU, posgresql 8.0.1.
Keyword split is a plperl function:
create or replace function keywords_split(text) returns text as $$
my $text = lc $_[0];
return $text;
$$
language plperl;
My problem is:
$ psql teszt;
Welcome to psql 8.0.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
teszt=# select lower('úéöÖÉÁ');
lower
úéööéá
(1 row)
teszt=# select keywords_split('AúéöÖÉÁ');
keywords_split
aúéöÖÉÁ
(1 row)
teszt=# select lower('úéöÖÉÁ');
lower
úéöÖÉÁ
(1 row)
Mage
---(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
[GENERAL] what's the best in 8.0
Hello, I found this: http://www.postgresql.org/community/survey.32 However, I think one of the bests improvement is that you don't have to typecast every value in pgsql8 to force it using indexes. Casting int4 values to int8 in case of bigint primary key was so annoying in 7.4. Mage ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] text and varchar
Hello, sorry for the trivial question. Is there any difference between varchar and text types in practice? I couldn't find. Mage ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Best Linux Distribution
I run Postgres on Gentoo and it works fine. By the way, I have to tell that the best linux is Gentoo. Mage ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] ext3
Hello, Gabor Szima asked us to translate the letter below. "I read that ext3 writeback mode is recommended for PostgreSQL. I made some tests. data=ordereddata=writeback -- restoredb: 2m16.790s1m42.367s UPDATE (17krows):9.289s7.147s UPDATE (17krows) (2.):10.480s3.778s VACUUM ANALYZE :9.364s0.986s ! VACUUM FULL :16.071s2.575s REINDEX TABLE :3.815s1.886s -- It's seductive. However I made some crash-tests too. Updated 4 tables simultaneously and recurring for 10 to 120s, then powered off the machine (without the reset button. i just pulled out the cable). SEQ RECOVERY-WARNINGS VACUUM --- 01: 1650OK(WARNING: invalid page header in block 769 of relation "18800"; zeroing out page) 02: 3FATAL(ERROR: could not access status of transaction 37814272) ---(DETAIL: could not open file "/data/pgdata/pg_clog/0024": No such file or directory) I have stopped my tests at this point because this is not for production use. The database was corrupted. With ordered mode I got this: ext3-noatime,data=ordered: SEQ RECOVERY-WARNINGS VACUUM -- 01: 0 OK 02: 0 OK 03: 0 OK 04: 0 W,OK(relation "" page 398 is uninitialized --- fixing) 05: 0 OK 06: 0 OK 07: 0 W,OK(relation "" page 911 is uninitialized --- fixing) 08: 0 OK 09: 0 OK 10: 0 OK -- I think that writeback mode first records the data then the inode, and the ordered mode does it in reverse order. I also mean that postgres log requires the inode recorded correctly, the data loss is handled by the WAL. AMD XP2000, 512MB RAM, PostgreSQL 7.4.6 (i686), linux-2.4.28, gcc-3.3.5, Adaptec 29160, WD Enterprise 4360 (SCSI, SCA-80) I made mkfs and initdb before every tests and I repeated them in reverse order too. No quake3 ran in the background. -Sygma" Sorry for my english. Mage ---(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
[GENERAL] multi column index and order by
Hello, "order by a asc b desc" how can I create an index for this? Mage ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] change natural column order
Tom Lane wrote: Richard Huxton <[EMAIL PROTECTED]> writes: I think you'll find you're out of luck. IIRC there was some discussion on the hackers list regarding a mapping layer that would let you re-order columns. I think the decision was "too much work for too small a gain". Yup, that was exactly the conclusion. Too much work and too much risk of introducing bugs (by using the wrong one of logical and physical column number in any given place). You really have to drop and recreate the table if you want to reorder the columns. This can be hard if you have foreign keys. I used dump, edit, and restore the whole database in the past. Mage ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] change natural column order
Tino Wildenhain wrote: Hi, Am Dienstag, den 30.11.2004, 10:26 +0100 schrieb Joolz: is it possible to change the natural order of the columns afterwards? The reason I need this is because the frontend picks up table columns in natural order, looks at the datatype and creates view, input and mutate (html) forms. Natural Order? This is similar to a select without order by - the SQL standard says nothing about a natural order. This is not true. Columns have an order. You can do INSERTs without specifying the columns. The values won't be inserted randomly but in their order. Changing the order of the columns is not a frontend question but logically. (Some) people would like to see relevant columns near to each other, even with an admin program. I would welcome some alter table column order feature. Btw human beings like changing things. Mage ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] VACUUM and ANALYZE Follow-Up
Joshua D. Drake wrote: In any case, it is hard to see how the present behaviour can be seen as desirable. It obviously causes problems at least for new Postgres users, and we all hope there will be many more of these folks in the future. Thanks for considering this. Mark Uhmmm... analyze or vacuum on an empty table is fairly pointless. Those utilities are supposed to be used on tables that have data. So the answer is, use them on tables that have data. Every 5th page of the manual says that I should use vacuum analyze frequently. There are two command line tools for this and another one in the contrib. I think none of them cares of empty tables. This issue need to be fixed. Mage ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] out of disk space
Hi, I know that the disk space of the database server should never run out but it can happen. Which is the worst case? May the database be corrupted? Mage -- http://mage.hu ---(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
[GENERAL] dealing with invalid date
Hi, can pgsql acceppt invalid date values? Sometimes it would be nice to convert 2003-02-29 to 2003-03-01 or to 2003-02-28 automatically instead of throwing back an error message. Mage ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pgsql 8 beta document
Alvaro Herrera wrote: On Mon, Oct 04, 2004 at 11:58:53PM +0200, Mage wrote: Is there any on the site? Or I shall find in the tar file? It's on http://developer.postgresql.org/docs/postgres Thank you. And is there some comparing 7.4 with 8.0 document? I mean a simple "new feature list" or something like that. Mage ---(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
[GENERAL] pgsql 8 beta document
Is there any on the site? Or I shall find in the tar file? Mage -- http://mage.hu ---(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: [GENERAL] Autoincremental value
[EMAIL PROTECTED] wrote: when insert rows: insert into table1 (field1) values (1); insert into table1 (field1) values (1); insert into table1 (field1) values (2); and then select * from table1, you get: field1| field2 --+--- 1 | 1 1 | 2 2 | 1 --+--- Do you mean: field1| field2 --+--- 1 | 1 1 | 2 2 | 3 --+--- You need the serial type. Mage -- http://mage.hu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Using view
Secrétariat wrote: Hello ! Why can't I update data when I use a VIEW instead of a TABLE ? Why can't you drive home if you drink whisky instead of coca-cola? Read the Manual Chapter 34 about the rule system. Mage ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Sql injection attacks
Bill Moran wrote: > > >Simply put: >1) If the untrusted value is a string, using a proper escape sequence should > make it safe. > > in pgsql (and mysql) you can escape almost everything. update table set a = '5' is corrent, even is column a is integer type. You can't escape the null value. Mage ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] eval in plpgsl
Bruce Momjian wrote: Uh, we have EXECUTE, but I don't know if will allow you to set a local variable. Maybe you can do: execute doesn't support select into Mage ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] boolean to int
Pavel Stehule wrote: Hello, you can use own cast. I think I have to create an own type too, because I don't want to use typecast in every select. You gave me the idea, thank you. Mage ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] boolean to int
Hello, I'm wondering why pgsql doesn't support boolean typecasts like select true::int; Many client applications including php assign 1 to true and 0 to false I see no use of pgsql boolean type with php, I use int2 or integer. Mage ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] recursive trigger
Tom Lane wrote:
You should just do
if new.parent <> old.parent then
new.name = ''old'';
As you have it, the inner UPDATE pre-empts the outer because it is
applied first. When control comes back from the trigger, the row
the trigger was handed is now dead (already updated) and can't be
updated again.
Okay, above is an easy example. My original conception was maintaining
the article_index (for sorting) this way:
create table article (
article_id bigserial primary key,
tree_id bigint not null,
article_index int,
article_name varchar
);
create or replace function article_index() returns trigger as '
declare
maxindex int;
begin
if TG_OP = ''INSERT'' then
select into maxindex article_index from article where tree_id =
new.tree_id order by article_index desc limit 1;
new.article_index = COALESCE(maxindex + 1, 1);
return new;
elsif TG_OP = ''UPDATE'' then
if new.tree_id <> old.tree_id then
select into maxindex article_index from article where tree_id =
new.tree_id order by article_index desc limit 1;
new.article_index = COALESCE(maxindex + 1, 1);
update article set article_index = article_index - 1 where
article_index > old.article_index and tree_id = old.tree_id and
article_id <> old.article_id; -- this won't work
end if;
return new;
elsif TG_OP = ''DELETE'' then
update article set article_index = article_index - 1 where
article_index > old.article_index and tree_id = old.tree_id;
return old;
end if;
end;
' language plpgsql;
create trigger article_index before insert or update or delete on
article for each row execute procedure article_index();
insert into article (article_name, tree_id) values ('a',1);
insert into article (article_name, tree_id) values ('b',1);
insert into article (article_name, tree_id) values ('c',1);
update article set tree_id = 2;
-
I don't understand, what's the problem, because the inner update never
updates the actual row fired the trigger. (the "old.article_id <>
article_id" condition is not necessary btw.). Does this mean, if i
change any other rows in a row level before update trigger, rows changed
won't be updated anymore in the same statement?
Mage
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] recursive trigger
Hi, what's wrong with this? --- create table test (id int, name text, parent int); insert into test values (1,'a',1); insert into test values (2,'b',1); insert into test values (3,'c',1); create or replace function test() returns trigger as ' begin raise info ''id: %, oldname: %'',old.id, old.name; if new.parent <> old.parent then update test set name = ''old'' where parent = old.parent; end if; return new; end; ' language plpgsql; create trigger test_trigger before update on test for each row execute procedure test(); update test set parent = 2; --- INFO: id: 1, oldname: old INFO: id: 1, oldname: old CONTEXT: PL/pgSQL function "test" line 4 at SQL statement INFO: id: 2, oldname: old CONTEXT: PL/pgSQL function "test" line 4 at SQL statement INFO: id: 3, oldname: old CONTEXT: PL/pgSQL function "test" line 4 at SQL statement UPDATE 0 - PgSQL 7.4.1 Mage ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
