[SQL] What does mod - in mod statement - stand for?
Hi, Could you please tell me what does mod - in mod statement - stand for? log_statement (string) Controls which SQL statements are logged. Valid values are none, ddl, mod, and all. (...). mod logs all ddl statements, plus data-modifying statements such as INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. (...) [http://www.postgresql.org/docs/current/interactive/runtime-config-logging.html] I do know about DDL, DML, DCL, which are commonly used in RBDMS, however I ignore about MOD, even if I understand now what it means from the documentation. If somebody can enlighten me. Thanks, -- Daniel http://www.majormode.com/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SQL stored function inserting and returning data in a row.
What about
$$
INSERT INTO ;
select currval('seq_matchmaking_session_id');
$$ language sql;
?
Indeed... :-( For some reason, I thought that it was not possible to
have to SQL statement in an SQL stored function.
By the way, is there any performance difference between pure SQL and
PL/pgSQL stored functions? If I remember correctly there was such a
distinction between pure SQL statement and PL/PLSQL stored procedures
(Oracle), in the sense that PL/PLSQL stored procedures are executed
within the PL/PLSQL engine which sends pure SQL statements to the SQL
engine for execution. There is a little overhead between PL/PLSQL and
SQL engines.
Regards,
--
Daniel
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL] SQL stored function inserting and returning data in a row.
Hi,
Is there any way to define a SQL stored function that inserts a row in a
table and returns the serial generated?
CREATE TABLE matchmaking_session
(
session_id bigint NOT NULL DEFAULT
nextval('seq_matchmaking_session_id'),
...
);
CREATE FUNCTION create_matchmaking_sesssion(...)
RETURNS bigint
AS $$
INSERT INTO matchmaking_session(...)
VALUES (...)
RETURNING session_id;
$$ LANGUAGE SQL;
2008-01-10 22:08:48 EST ERROR: return type mismatch in function
declared to return bigint
2008-01-10 22:08:48 EST DETAIL: Function's final statement must be a
SELECT.
2008-01-10 22:08:48 EST CONTEXT: SQL function
create_matchmaking_sesssion
I can easily convert this code into a PL/pgSQL function, but I'm
thinking that pure SQL is more natural (and faster?) for such a stored
function.
Regards,
--
Daniel
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Using schema
Hi, I tried to figure out how to select a particular schema before executing statement on its objects without having to specify the schema in these statements (cf. the instruction USE supported in some other RDBMS) PostgreSQL allows modifying dynamically the schema search path: SET search_path TO my_schema, public; http://www.postgresql.org/docs/current/static/ddl-schemas.html http://www.postgresql.org/docs/current/static/sql-set.html By doing so, the creation statements will be executed in the schema my_schema. SET search_path TO public; CREATE TABLE foo(i int); SET search_path TO my_schema, public; CREATE TABLE foo(i int); List of relations Schema | Name | Type |Owner ---+--+- public| bar | table| dbo_ubilive my_schema | foo | table| dbo_ubilive The cool thing with this, compared to the USE statement supported by some other RDBMS, is that the user is not restricted to one given schema without explicit schema declaration: SELECT * FROM foo; -- Uses schema my_schema SELECT * FROM bar; -- Uses schema public SELECT * FROM foo, bar WHERE foo.i = bar.i; -- Uses both schemas That is damn flexible! :-) -- Daniel ---(end of broadcast)--- TIP 1: 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] Strang behaviour SELECT ... LIMIT n FOR UPDATE
-Message d'origine- De : Tom Lane [mailto:[EMAIL PROTECTED] Envoyé : mardi, novembre 27, 2007 23:46 À : Daniel Caune Cc : pgsql-sql@postgresql.org Objet : Re: [SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE Daniel Caune [EMAIL PROTECTED] writes: I'm facing a strange behaviour with a statement SELECT ... LIMIT n FOR UPDATE in PostgreSQL 8.1. The number of rows returned is actually (n - 1). I'm trying to find whether this is an identified issue with PostgreSQL 8.1 that might have been fixed in a later version such as 8.2; I don't have any problem in moving to a later version if needed. There's no known issue specifically of that form (and a quick test of 8.1 doesn't reproduce any such behavior). However, it is known and documented that LIMIT and FOR UPDATE behave rather oddly together: the LIMIT is applied first, which means that if FOR UPDATE rejects any rows as being no longer up-to-date, you get fewer than the expected number of rows out. You did not mention any concurrent activity in your example, but I'm betting there was some ... regards, tom lane Yes, you were betting right. However I would have thought that the SELECT ... FOR UPDATE statement blocks if another process were locking the same rows. The record values don't change from a call to another. I did read the documentation, especially the section that Bruce Momjian's pointed me out, but I don't think that it corresponds to this case (cf. my test). I did the following test, removing all the where-clause from the SELECT statement. Every statement completes immediately, i.e. it doesn't block. agoratokens= select id from Tokens id - 47 104 44 42 33 69 94 89 90 ... Time: 119.314 ms agoratokens= select id from Tokens limit 2 for update; id - 47 104 (2 rows) Time: 17.679 ms agoratokens= select id from Tokens limit 3 for update; id - 47 104 (2 rows) Time: 20.452 ms The statement doesn't return the row where id equals to 44. agoratokens= select id from Tokens limit 3; id - 47 104 44 (3 rows) Time: 1.186 ms The statement returns the row where id equals to 44. agoratokens= select id from Tokens limit 3 for update; id - 47 104 (2 rows) Time: 9.473 ms The statement still doesn't return the row where id equals to 44. agoratokens= select id from Tokens where id IN (47, 104, 44, 42) limit 3 for update; id - 47 104 44 (3 rows) This time, the statement returns the row where id equals to 44. agoratokens= select id from Tokens limit 3; id - 47 104 44 (3 rows) Time: 7.547 ms agoratokens= select id from Tokens limit 5 for update; id - 47 104 33 (3 rows) Time: 11.725 ms This time, the statement doesn't return the rows where id equals to 44 and 42. agoratokens= select id from Tokens limit 8 for update; id - 47 104 33 69 94 89 (6 rows) Time: 11.794 ms The statement still doesn't return the rows where id equals to 44 a 42. agoratokens= select id from Tokens where id = 44 limit 3 for update; id 44 (1 row) Time: 14.172 ms The statement does return the row where id equals to 44. However, it is known and documented that LIMIT and FOR UPDATE behave rather oddly together: the LIMIT is applied first, which means that if FOR UPDATE rejects any rows as being no longer up-to-date, you get fewer than the expected number of rows out. Tom, when you say rows as being no longer up-to-date, do you mean which values don't match anymore the where-clauses of the SELECT statement? If so, that doesn't correspond to my test since I remove every where-clause. Any ideas, any other tests I can try? Thanks, -- Daniel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE
De : Tom Lane [mailto:[EMAIL PROTECTED]
Daniel Caune [EMAIL PROTECTED] writes:
I did the following test, removing all the where-clause from the SELECT
statement. Every statement completes immediately, i.e. it doesn't block.
I think you left out some critical information, like who else was doing
what to the table.
What it looks like to me is that the third and fourth rows in this view
were live according to your transaction snapshot, but were committed
dead as of current time, and so FOR UPDATE wouldn't return them.
agoratokens= select id from Tokens where id IN (47, 104, 44, 42)
limit 3 for update;
This time, the statement returns the row where id equals to 44.
No, it returns *some* row where id equals 44. Not necessarily the same
one seen in the seqscan. (I imagine this query is using an index, and
so would visit rows in a different physical order.) Printing the ctid
of the rows would confirm or disprove that theory.
regards, tom lane
Thanks Tom. I think this time you will point me out the problem. The column
id has a primary key constraint on. There should not be more than one row with
id equals to 44.
agoratokens= \d Tokens
Table public.Tokens
Column | Type | Modifiers
---++---
id| integer| not null default
nextval('Tokens_id_seq'::regclass)
type | integer| not null
value | character varying(255) | not null
isLocked | boolean| not null default false
timestamp | timestamp(6) without time zone |
Indexes:
Tokens_pkey PRIMARY KEY, btree (id)
(...)
agoratokens= select ctid, * from Tokens where isLocked = true limit 3 for
update;
ctid | id | type | value | isLocked | timestamp
--++--+---+--+---
(0 rows)
agoratokens= select ctid, * from Tokens where isLocked = true limit 3;
ctid| id | type | value | isLocked | timestamp
---++--+---+--+---
(199,84) | 44 |3 | 3 | t| 2007-04-03 12:12:02.46944
(199,114) | 42 |3 | 1 | t| 2007-04-03 13:00:44.877
(2 rows)
agoratokens= select ctid, * from Tokens where id = 44;
ctid| id | type | value | isLocked | timestamp
---++--+---+--+
(3702,85) | 44 |3 | 3 | f| 2007-11-22 16:41:33.494371
(1 row)
agoratokens= select count(*) from Tokens where id = 44;
count
---
1
(1 row)
It seems that, in certain condition, row (199,84) is shadowing row (3702,85);
my feeling from a customer high level. Indeed, as a PostgreSQL core
developer, that assertion could make you laugh... :-)
I took into account your point about the concurrent context. Therefore I
isolated the database from any connection except mine.
# TYPE DATABASE USER IP-ADDRESS IP-MASKMETHOD
local all all trust
hostall all 127.0.0.1 255.255.255.0 password
#hostall all 10.3.41.0 255.255.254.0 password
sudo /etc/init.d/postgresql-8.1 restart
* Restarting PostgreSQL 8.1 database server [ ok ]
No other client than my psql was connected to PostgreSQL. You can trust me.
The result is exactly the same:
agoratokens= select ctid, * from Tokens where isLocked = true limit 3;
ctid| id | type | value | isLocked | timestamp
---++--+---+--+---
(199,84) | 44 |3 | 3 | t| 2007-04-03 12:12:02.46944
(199,114) | 42 |3 | 1 | t| 2007-04-03 13:00:44.877
(2 rows)
agoratokens= select ctid, * from Tokens where id = 44;
ctid| id | type | value | isLocked | timestamp
---++--+---+--+
(3702,85) | 44 |3 | 3 | f| 2007-11-22 16:41:33.494371
(1 row)
agoratokens= select ctid, * from Tokens where isLocked = true limit 3;
ctid| id | type | value | isLocked | timestamp
---++--+---+--+---
(199,84) | 44 |3 | 3 | t| 2007-04-03 12:12:02.46944
(199,114) | 42 |3 | 1 | t| 2007-04-03 13:00:44.877
(2 rows)
agoratokens= select ctid, * from Tokens where id = 44;
ctid| id | type | value | isLocked | timestamp
---++--+---+--+
(3702,85) | 44 |3 | 3 | f| 2007-11-22 16:41:33.494371
(1 row)
agoratokens= select count(*) from Tokens where id = 44;
count
---
1
(1 row)
By the way, according to the business logic, the timestamp 2007-04-03
12:12:02.46944 is weird, because too old. I apologize if my question is
stupid because
[SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE
Hi, I'm facing a strange behaviour with a statement SELECT ... LIMIT n FOR UPDATE in PostgreSQL 8.1. The number of rows returned is actually (n - 1). I'm trying to find whether this is an identified issue with PostgreSQL 8.1 that might have been fixed in a later version such as 8.2; I don't have any problem in moving to a later version if needed. agoratokens= SELECT * FROM Tokens INNER JOIN Tokentypes ON Tokens.type=Tokentypes.type WHERE Tokentypes.tokenName ='clanName' AND Tokens.isLocked = false limit 2 FOR UPDATE; id | type | value | isLocked | timestamp | type | tokenName -+--+--+--+- ---+--+--- 104 |2 | RegressionTestClanName13 | f| 2007-11-27 20:40:25.208074 |2 | clanName (1 row) agoratokens= SELECT * FROM Tokens INNER JOIN Tokentypes ON Tokens.type=Tokentypes.type WHERE Tokentypes.tokenName ='clanName' AND Tokens.isLocked = false limit 3 FOR UPDATE; id | type | value | isLocked | timestamp | type | tokenName -+--+--+--+- ---+--+--- 104 |2 | RegressionTestClanName13 | f| 2007-11-27 20:40:25.208074 |2 | clanName 118 |2 | RegressionTestClanName28 | f| 2007-11-21 21:10:29.872352 |2 | clanName (2 rows) If I remove the FOR UPDATE clause, the SELECT ... LIMIT n statement returns n rows as expected: agoratokens= SELECT * FROM Tokens INNER JOIN Tokentypes ON Tokens.type=Tokentypes.type WHERE Tokentypes.tokenName ='clanName' AND Tokens.isLocked = false limit 3; id | type | value | isLocked | timestamp | type | tokenName -+--+--+--+- ---+--+--- 104 |2 | RegressionTestClanName13 | f| 2007-11-27 20:40:25.208074 |2 | clanName 40 |2 | RegressionTestClanName9 | f| 2007-10-15 11:27:31.897|2 | clanName 118 |2 | RegressionTestClanName28 | f| 2007-11-21 21:10:29.872352 |2 | clanName (3 rows) -- Daniel ---(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
[SQL] Erlang PostgreSQL native driver
Hi,
We are using Erlang, PostgreSQL 8.2.5, and the Erlang library pgsql
developed by Christian Sunesson
(http://jungerl.cvs.sourceforge.net/jungerl/jungerl/lib/pgsql/).
The Erlang library pgsql is easy to use and does the job so far, at
least for our prototyping phase. However, this library has a problem
with stored function that returns nothing such as:
CREATE OR REPLACE FUNCTION foo()
RETURNS void
AS $$
...
The problem seems to happen when the Erlang library pgsql tries to
decode the result value return by the stored function, which is void.
The transaction has been successfully committed in database.
([EMAIL PROTECTED])3 pgsql:squery(Connection, SELECT foo()).
=ERROR REPORT 15-Nov-2007::11:40:25 ===
Error in process 0.38.0 on node '[EMAIL PROTECTED]' with exit value:
{{nocatch,{unknown_oid,2278}},[{pgsql_util,decode_oid,1},{pgsql_util,dec
ode_descs,1},{pgsql_proto,process_squery,1},{pgsql_proto,idle,2}]}
** exited: {{nocatch,{unknown_oid,2278}},
[{pgsql_util,decode_oid,1},
{pgsql_util,decode_descs,1},
{pgsql_proto,process_squery,1},
{pgsql_proto,idle,2}]} **
I don't think this is a big issue. According to the error
(unknown_oid), I suppose that the pgsql library doesn't support void
type yet. We should be able to fix easily the library.
However, because our prototyping phase is close to the end, we are
thinking to use another library that would perhaps better fit production
constraints (pool, etc.). We have tested the PostgreSQL ODBC driver but
we have faced some issues on Windows (Unicode, PostgreSQL data type
support).
We are interesting in having some feedback from people who use both
Erlang and PostgreSQL. Which PostgreSQL driver do you use? Does
someone use the PostgreSQL driver from Erlang Consulting? Are there
other native PostgreSQL driver?
Thanks,
--
Daniel
---(end of broadcast)---
TIP 1: 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] Calling void functions
I'm informed that the last statement of a function that returns void cannot be a SELECT. How else is one supposed to call another function which also returns void? PERFORM ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Calling void functions
PERFORM works only in plpgsql, Peter wrote a pl/sql-function... Oups, sorry! I missed the point. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Sequence vs Serial
Hi,
I was wondering when it is better to choose sequence, and when it is better
to use serial. The serial type is a sequence with default parameters
(http://www.postgresql.org/docs/8.2/static/datatype-numeric.html#DATATYPE-SE
RIAL). Actually, I never use serial - I prefer sequence for some reasons
that I explain later in this electronic mail - but I may be wrong. It's
never late to learn!
There is not that much difference between using sequence or serial when the
current value needs to be returned (which is most of my cases).
Using a sequence [1]:
SELECT INTO v_mytable_id nextval('seq_mytable_id');
INSERT INTO mytable(id, mycolumn1, mycolumn2)
VALUES (v_mytable_id, v_mycolumn1, v_mycolumn2);
RETURN v_mytable_id;
Using a serial:
INSERT INTO mytable(mycolumn1, mycolumn2)
VALUES (v_mycolumn1, v_mycolumn2);
SELECT INTO v_mytable_id currval('mytable_id_seq');
RETURN v_mytable_id;
I would say that these two code snippets have equivalent performance, if
sequence seq_mytable_id has been created with default parameters.
The reason why I generally prefer sequence over serial is that it is
possible to tune sequence, which it seems to not be possible while using
serial. For instance, the sequence allows defining a cache: the optional
clause CACHE specifies how many sequence numbers are to be preallocated and
stored in memory for faster access. When inserting a lot of entities in a
few times, that should make a big difference!
Now, when is it better to use serial? Serial is easier to define and to
use. I would say that serial could be used to insert entities in dimension
tables (such as a table that references countries, and where a unique
identifier must be defined), a table which data are inserted when the
operational system is setup. However, using serial for fact tables (such as
a table that stores player actions in an MMO game) seems to not be that much
relevant.
What are your experiences?
Regards,
[1] It is possible to use another form, similar to serial:
CREATE SEQUENCE seq_mytable_id;
CREATE TABLE v_mytable(id int nextval('seq_mytable_id'), mycolumn1 int ,
mycolumn2 int);
Then it is possible to use the sequence as follows:
INSERT INTO mytable(mycolumn1, mycolumn2)
VALUES (v_mycolumn1, v_mycolumn2);
SELECT INTO v_mytable_id currval('seq_mytable_id');
RETURN v_mytable_id;
--
Daniel
Re: [SQL] help with version checking
I tried to do the following in PostgreSQL: DECLARE v_version VARCHAR; BEGIN SELECT version INTO v_version FROM version WHERE id = 1; IF v_version ''1.0.0.0'' THEN RAISE EXCEPTION ''This script needs Agenda version 1.0.0.0, detected version %'', v_version; END IF; END; //The upgrade stuff but when I execute it, gives a lot of errors: psql -d dermagier -f upgrade_agenda.sql psql:upgrade_agenda.sql:2: ERROR: syntax error at or near VARCHAR at character 21 psql:upgrade_agenda.sql:5: ERROR: syntax error at or near SELECT at character 9 psql:upgrade_agenda.sql:8: ERROR: syntax error at or near IF at character 3 psql:upgrade_agenda.sql:9: ERROR: syntax error at or near IF at character 7 psql:upgrade_agenda.sql:11: WARNING: there is no transaction in progress COMMIT Anybody knows how I can do this or which is the best way to do it? You should define a PL/PGSQL function such as: CREATE OR REPLACE FUNCTION check_version() RETURNS void AS $$ DECLARE v_version VARCHAR; BEGIN SELECT version INTO v_version FROM version WHERE id = 1; IF v_version '1.0.0.0' THEN RAISE EXCEPTION 'This script needs Agenda version 1.0.0.0, detected version %', v_version; END IF; END; $$ LANGUAGE PLPGSQL; Regards, -- Daniel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] DELETE RETURNING
Hi, I have read in the future PostgreSQL 8.2 release note (http://developer.postgresql.org/pgdocs/postgres/release-8-2.html) that DELETE RETURNING will be supported. Tom, could please confirm that the following PL/PGSQL statements will work on PostgreSQL 8.2? INSERT INTO a(x, y) DELETE FROM b RETURNING x, current_timestamp; IF NOT FOUND THEN -- No data were deleted actually END IF; Thanks, -- Daniel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] conversion of numeric field from MSSQL to postgres
De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Kenneth Gonsalves Envoyé : 15 octobre 2006 08:31 À : pgsql-sql@postgresql.org Objet : [SQL] conversion of numeric field from MSSQL to postgres hi, am migrating a database from MSSQL to postgres. How would i migrate this: [Id] [numeric](18, 0) IDENTITY (1, 1) It seems that you've already asked for this question last Sunday, and because your question is somewhat deterministic, the answers are more likely to be the same. Check your previous e-mails. -- Daniel ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] migrating numeric to serial from MSSQL to postgres
hi, am migrating a database from MSSQL to postgres. How would i migrate this: [Id] [numerc](18, 0) IDENTITY (1, 1) You might want to create a sequence first, such as with more or less options: CREATE SEQUENCE my_sequence INCREMENT BY 1 MINVALUE 1 NO MAXVALUE START WITH 1 CACHE 1 NO CYCLE; Then you should be able to migrate your code to something like: Id INTEGER NOT NULL DEFAULT NEXTVAL(my_sequence') -- Daniel ---(end of broadcast)--- TIP 1: 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
[SQL] Evaluation of if conditions
Hi, How does the IF statement evaluate conditions? Does it evaluate conditions following their declaration order from left to right? In case of or-conditions, does the IF statement stop evaluating conditions whenever a first or-condition is true? The following snippet seems to be invalid, which let me think that PL/PGSQL evaluates all the conditions: IF (TG_OP = 'INSERT') OR (OLD.bar = ...) THEN statement END IF; Should be rewritten as (for example): IF (TG_OP = 'INSERT') THEN statement ELSIF (OLD.bar = ...) THEN statement END IF; Regards, -- Daniel ---(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
[SQL] SQL92 compliance
Hi, Is AS in SELECT my_column AS my_name FROM my_table mandatory to be SQL92 compliant? PostgreSQL requires this keyword by default when defining alias, which might be good thing. I mean, I would prefer being notified from a syntax error than spending a couple of hours wondering why a client application, at the end of a several data marshalling processes, doesn't get the right data... For instance MySQL doesn't require it and sometimes that sucks: SELECT my_column1 my_column2, my_column3 FROM my_table Regards, -- Daniel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] PostgreSQL server terminated by signal 11
-Message d'origine- De : Tom Lane [mailto:[EMAIL PROTECTED] Envoyé : jeudi, juillet 27, 2006 19:26 À : Daniel Caune Cc : pgsql-admin@postgresql.org; pgsql-sql@postgresql.org Objet : Re: [SQL] PostgreSQL server terminated by signal 11 Daniel Caune [EMAIL PROTECTED] writes: I run the command responsible for creating the index and I entered continue in gdb for executing the command. After a while, the server crashes: Program received signal SIGSEGV, Segmentation fault. 0x08079e2a in slot_attisnull () (gdb) Continuing. Program terminated with signal SIGSEGV, Segmentation fault. The program no longer exists. I can't do bt since the program no longer exists. I think you typed one carriage return too many and the thing re-executed the last command, ie, the continue. Try it again. You were right. Program received signal SIGSEGV, Segmentation fault. 0x08079e2a in slot_attisnull () (gdb) bt #0 0x08079e2a in slot_attisnull () #1 0x0807a1d0 in slot_getattr () #2 0x080c6c73 in FormIndexDatum () #3 0x080c6ef1 in IndexBuildHeapScan () #4 0x0809b44d in btbuild () #5 0x0825dfdd in OidFunctionCall3 () #6 0x080c4f95 in index_build () #7 0x080c68eb in index_create () #8 0x08117e36 in DefineIndex () #9 0x081db4ee in ProcessUtility () #10 0x081d8449 in PostgresMain () #11 0x081d99d5 in PortalRun () #12 0x081d509e in pg_parse_query () #13 0x081d6c33 in PostgresMain () #14 0x081aae91 in ClosePostmasterPorts () #15 0x081ac14c in PostmasterMain () #16 0x08168f22 in main () -- Daniel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] PostgreSQL server terminated by signal 11
De : Tom Lane [mailto:[EMAIL PROTECTED] Envoyé : vendredi, juillet 28, 2006 09:38 À : Daniel Caune Cc : pgsql-admin@postgresql.org; pgsql-sql@postgresql.org Objet : Re: [SQL] PostgreSQL server terminated by signal 11 Daniel Caune [EMAIL PROTECTED] writes: Program received signal SIGSEGV, Segmentation fault. 0x08079e2a in slot_attisnull () (gdb) bt #0 0x08079e2a in slot_attisnull () #1 0x0807a1d0 in slot_getattr () #2 0x080c6c73 in FormIndexDatum () #3 0x080c6ef1 in IndexBuildHeapScan () #4 0x0809b44d in btbuild () #5 0x0825dfdd in OidFunctionCall3 () #6 0x080c4f95 in index_build () #7 0x080c68eb in index_create () #8 0x08117e36 in DefineIndex () Hmph. gdb is lying to you, because slot_getattr doesn't call slot_attisnull. This isn't too unusual in a non-debug build, because the symbol table is incomplete (no mention of non-global functions). Given that this doesn't happen right away, but only after it's been processing for awhile, we can assume that FormIndexDatum has been successfully iterated many times already, which seems to eliminate theories like the slot or the keycol value being bogus. I'm pretty well convinced now that we're looking at a problem with corrupted data. Can you do a SELECT * FROM (or COPY FROM) the table without error? regards, tom lane The statement copy gslog_event to stdout; leads to ERROR: invalid memory alloc request size 4294967293 after awhile. (...) 354964834 2006-07-19 10:53:42.813+00 (...) 354964835 2006-07-19 10:53:44.003+00 (...) ERROR: invalid memory alloc request size 4294967293 I tried then select * from gslog_event where gslog_event_id = 354964834 and gslog_event_id = 354964900;: 354964834 | 2006-07-19 10:53:42.813+00 | (...) 354964835 | 2006-07-19 10:53:44.003+00 | (...) 354964837 | 2006-07-19 10:53:44.113+00 | (...) 354964838 | 2006-07-19 10:53:44.223+00 | (...) (...) (66 rows) The statement select * from gslog_event; leads to Killed... Ouch! The psql client just exits (the postgres server crashes too)! The statement select * from gslog_event where gslog_event_id = 354964834; passed. I did other tests on some other tables that contain less data but that seem also corrupted: copy player to stdout ERROR: invalid memory alloc request size 1918988375 select * from player where id =771042 and id=771043; ERROR: invalid memory alloc request size 1918988375 select max(length(username)) from player; ERROR: invalid memory alloc request size 1918988375 select max(length(username)) from player where id = 771042; max - 15 select max(length(username)) from player where id = 771050; max - 15 select max(length(username)) from player where id = 771044 and id = 771050; max - 13 Finally: select * from player where id=771043; ERROR: invalid memory alloc request size 1918988375 select id from player where id=771043; id 771043 (1 row) agora= select username from player where id=771043; ERROR: invalid memory alloc request size 1918988375 I'm also pretty much convinced that there are some corrupted data, especially varchar row. Before dropping corrupted rows, is there a way to read part of corrupted data? Thanks Tom for your great support. I'm just afraid that I wasted your time... Anyway I'll write a FAQ that provides some information about this kind of problem we have faced. Regards, -- Daniel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] PostgreSQL server terminated by signal 11
Hi, My PostgreSQL server running on a Linux machine is terminated by signal 11 whenever I try to create some indexes on a table, which contains quite a lot of data. However I succeeded in creating some other indexes without having the PostgreSQL server terminated: agora= CREATE INDEX IDX_GSLOG_EVENTTIME agora- ON GSLOG_EVENT (EVENT_DATE_CREATED); CREATE INDEX Time: 152908.797 ms agora= explain analyze select max(event_date_created) from gslog_event; QUERY PLAN -- Result (cost=3.80..3.81 rows=1 width=0) (actual time=0.218..0.221 rows=1 loops=1) InitPlan - Limit (cost=0.00..3.80 rows=1 width=8) (actual time=0.197..0.200 rows=1 loops=1) - Index Scan Backward using idx_gslog_eventtime on gslog_event (cost=0.00..39338251.59 rows=10348246 width=8) (actual time=0.188..0.188 rows=1 loops=1) Filter: (event_date_created IS NOT NULL) Total runtime: 0.324 ms (6 rows) Time: 41.085 ms agora= CREATE INDEX IDX_GSLOG_EVENT_SPREAD_PROTOCOL_NAME agora- ON GSLOG_EVENT (EVENT_DATE_CREATED) agora- WHERE EVENT_NAME::text 'player-login'::text agora- AND PLAYER_USERNAME IS NOT NULL agora- AND GAME_CLIENT_VERSION IS NULL; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. The PostgreSQL log file doesnt give more information about what went wrong, except that the server process has been terminated: LOG: server process (PID 22270) was terminated by signal 11 LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing FATAL: the database system is starting up LOG: database system was interrupted at 2006-07-27 15:29:27 GMT LOG: checkpoint record is at 249/179D44A8 LOG: redo record is at 249/179D44A8; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 543712876; next OID: 344858 LOG: next MultiXactId: 2; next MultiXactOffset: 3 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 249/179D44EC LOG: record with zero length at 249/179E4888 LOG: redo done at 249/179E2DFC LOG: database system is ready LOG: transaction ID wrap limit is 2147484146, limited by database postgres I checked the memory installed on the machine, running memtest86 during more than one day; no error found. I checked bad blocks on every hard drive installed in this machine, using e2fsck -c /dev/hdxx; no bad block found. Ive already dropped the table, inserted data, and tried to create all the indexes. The server systematically crashed when creating some specific indexes. The only idea I have for the moment would be to setup another machine with the same database environment. Other idea(s)? Thanks -- Daniel CAUNE Ubisoft Online Technology (514) 490 2040 ext. 3613
Re: [SQL] PostgreSQL server terminated by signal 11
De : Tom Lane [mailto:[EMAIL PROTECTED] Envoyé : jeudi, juillet 27, 2006 16:06 À : Daniel Caune Cc : pgsql-sql@postgresql.org Objet : Re: [SQL] PostgreSQL server terminated by signal 11 Daniel Caune [EMAIL PROTECTED] writes: My PostgreSQL server running on a Linux machine is terminated by signal 11 whenever I try to create some indexes on a table, which contains quite a lot of data. Judging from your examples it's got something to do with the partial index WHERE clause. What PG version is this exactly? If you leave out different parts of the WHERE, does it still crash? Does the crash happen immediately after you give the command, or does it run for awhile? It might be worth getting a stack trace from the failure (best way is to attach to the running backend with gdb, provoke the crash, and do bt --- search for gdb in the archives if you need details). regards, tom lane The postgres server version is 8.1.4. Yes, if leave the WHERE clause a simple index, I don't encounter any problem: CREATE INDEX IDX_GSLOG_EVENTTIME ON GSLOG_EVENT (EVENT_DATE_CREATED); Anyway, I'm not sure, Tom, that is only related to the WHERE clause as crash occur with composite index too, such as: CREATE INDEX IDX_GSLOG_EVENT_PLAYER_EVENT ON GSLOG_EVENT (PLAYER_USERNAME, EVENT_NAME); The crash may happen a while after sending the command. For example, supposing I reboot the Linux machine and I immediately run the command (i.e. most of memory is unused), it takes more than five minutes before crash occurs. At such time the memory usage is the following (top every second): Mem: 2075860k total, 1787600k used, 288260k free, 6300k buffers Swap: 369452k total,0k used, 369452k free, 1748032k cached When reconnecting to the new postgres respawn, it takes approximately the same time for having it crashing, whatever the number of times I proceed like this. I did some other tests trying to detect any common denominator that may make the postgres server crashing. Here some results are: select max(length(game_client_version)) from gslog_event; = [CRASH] select max(length(game_client_version)) from gslog_event where game_client_version is not null; = [OK, max = 28] select count(*) from gslog_event where length(game_client_version) = 0; = [OK, count = 4463726] select count(*) from gslog_event where upper(game_client_version) = 'FARCRYPC1.33'; = [OK, count = 576318] select count(*) from gslog_event where lower(player_username) = 'lythanhphu'; = [CRASH] I was thinking about nullable value, but finally, you know what? I have strictly no idea! :-) I'll look at the archive for running postgres with gdb and provide more accurate information. Thanks, -- Daniel ---(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: [SQL] PostgreSQL server terminated by signal 11
-Message d'origine- De : Tom Lane [mailto:[EMAIL PROTECTED] Envoyé : jeudi, juillet 27, 2006 16:06 À : Daniel Caune Cc : pgsql-sql@postgresql.org Objet : Re: [SQL] PostgreSQL server terminated by signal 11 Daniel Caune [EMAIL PROTECTED] writes: My PostgreSQL server running on a Linux machine is terminated by signal 11 whenever I try to create some indexes on a table, which contains quite a lot of data. Judging from your examples it's got something to do with the partial index WHERE clause. What PG version is this exactly? If you leave out different parts of the WHERE, does it still crash? Does the crash happen immediately after you give the command, or does it run for awhile? It might be worth getting a stack trace from the failure (best way is to attach to the running backend with gdb, provoke the crash, and do bt --- search for gdb in the archives if you need details). regards, tom lane Quite a long time I didn't use gdb! :-) Anyway I proceed as described hereafter; correct me if I was wrong. ps -eaf | grep postgres postgres 2792 2789 0 21:50 pts/200:00:00 su postgres postgres 2793 2792 0 21:50 pts/200:00:00 bash postgres 2902 1 7 22:17 ?00:01:10 postgres: dbo agora [local] idle postgres 2952 1 2 22:32 ?00:00:00 /usr/lib/postgresql/8.1/bin/postmaster -D /var/lib/postgresql/8.1/main -c unix_socket_directory=/var/run/postgresql -c config_file=/etc/postgresql/8.1/main/postgresql.conf -c hba_file=/etc/postgresql/8.1/main/pg_hba.conf -c ident_file=/etc/postgresql/8.1/main/pg_ident.conf postgres 2954 2952 0 22:32 ?00:00:00 postgres: writer process postgres 2955 2952 0 22:32 ?00:00:00 postgres: stats buffer process postgres 2956 2955 0 22:32 ?00:00:00 postgres: stats collector process I connected to the postgres server using psql and I retrieved the backend pid by executing the statement SELECT pg_backend_pid(); I started gdb under the UNIX account postgres and I attached the backend process providing the pid returned by the statement. I run the command responsible for creating the index and I entered continue in gdb for executing the command. After a while, the server crashes: Program received signal SIGSEGV, Segmentation fault. 0x08079e2a in slot_attisnull () (gdb) Continuing. Program terminated with signal SIGSEGV, Segmentation fault. The program no longer exists. I can't do bt since the program no longer exists. How can I provide more information, stack trace, and so on? -- Daniel ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] PostgreSQL server terminated by signal 11
-Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Tom Lane Envoyé : jeudi 27 juillet 2006 19:26 À : Daniel Caune Cc : pgsql-admin@postgresql.org; pgsql-sql@postgresql.org Objet : Re: [SQL] PostgreSQL server terminated by signal 11 Daniel Caune [EMAIL PROTECTED] writes: I run the command responsible for creating the index and I entered continue in gdb for executing the command. After a while, the server crashes: Program received signal SIGSEGV, Segmentation fault. 0x08079e2a in slot_attisnull () (gdb) Continuing. Program terminated with signal SIGSEGV, Segmentation fault. The program no longer exists. I can't do bt since the program no longer exists. I think you typed one carriage return too many and the thing re-executed the last command, ie, the continue. Try it again. OK, I'll try that tomorrow morning. Perhaps can I set a conditional breakpoint to function slot_attisnull when parameter slot is null (or slot-tts_tupleDescriptor is null). The lack of arguments shown for slot_attisnull suggests that all we're going to get is a list of function names, without line numbers or argument values. If that's not enough to figure out the problem, can you rebuild with --enable-debug to get a more useful stack trace? Well, I installed PostgreSQL using apt-get but that won't be a problem to get the source from the CVS repository and to build a postgres binary using the option you provide to me. Just let me the time to do that. :-) Thanks, -- Daniel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] PostgreSQL server terminated by signal 11
-Message d'origine-
De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
De la part de D'Arcy J.M. Cain
Envoyé : jeudi 27 juillet 2006 19:49
À : Daniel Caune
Cc : [EMAIL PROTECTED]; pgsql-admin@postgresql.org; pgsql-
[EMAIL PROTECTED]
Objet : Re: [SQL] PostgreSQL server terminated by signal 11
On Thu, 27 Jul 2006 19:00:27 -0400
Daniel Caune [EMAIL PROTECTED] wrote:
I run the command responsible for creating the index and I entered
continue in gdb for executing the command. After a while, the server
crashes:
Program received signal SIGSEGV, Segmentation fault.
0x08079e2a in slot_attisnull ()
That's a pretty small function. I don't see much room for error. This
diff in src/backend/access/common/heaptuple.c seems like the most
likely place to catch it.
RCS file: /cvsroot/pgsql/src/backend/access/common/heaptuple.c,v
retrieving revision 1.110
diff -u -p -u -r1.110 heaptuple.c
--- heaptuple.c 14 Jul 2006 14:52:16 - 1.110
+++ heaptuple.c 27 Jul 2006 23:37:54 -
@@ -1470,8 +1470,13 @@ slot_getsomeattrs(TupleTableSlot *slot,
bool
slot_attisnull(TupleTableSlot *slot, int attnum)
{
- HeapTuple tuple = slot-tts_tuple;
- TupleDesc tupleDesc = slot-tts_tupleDescriptor;
+ HeapTuple tuple;
+ TupleDesc tupleDesc;
+
+ assert(slot != NULL);
+
+ tuple = slot-tts_tuple;
+ tupleDesc = slot-tts_tupleDescriptor;
/*
* system attributes are handled by heap_attisnull
Of course, you still have to find out what's calling it with slot set
to NULL if that turns out to be the problem. It may also be that slot
is not NULL but set to garbage. You could also add a notice there.
Two, in fact. One to display the address of slot and one to display
the value of slot-tts_tuple or slot-tts_tupleDescriptor. If the
first shows a non NULL value and the second causes your crash that
tells you that the value of slot is probably trashed before
calling the function.
Yes, I was afraid to go that deeper, but it's time! :-))
Actually it seems, from the source code, that a null slot-tts_tuple won't lead
to a segmentation fault in function slot_attisnull, while slot and
slot-tts_tupleDescriptor will. I will trace the function trying to discover
what goes wrong behind the scene.
Do this in conjunction with Tom Lane suggestion of --enable-debug for
more information.
OK
--
Daniel
---(end of broadcast)---
TIP 1: 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
[SQL] Trigger, record old is not assigned yet
Hi, Ive created a trigger BEFORE INSERT OR UPDATE on a table and, indeed, when the trigger is raised before insertion the record old is not assigned. Is there a way to distinguish in the trigger procedure from an insert statement to an update statement? Regards, -- Daniel CAUNE Ubisoft Online Technology (514) 490 2040 ext. 3613
[SQL] Invalid memory alloc request size
Hi, I'm using PostgreSQL 8.1.4 on a Linux 2.6.8-2-686-smp machine, 2 Go memory. I have a strange error invalid memory alloc request size, when I perform the following query: = select * from player where lower(username) = 'wario'; ERROR: invalid memory alloc request size 1918988375 where player contains a bit more than 1 million records, username is a character varying(255) not null. I have strictly no idea about what goes wrong here. Whatever operation I try to apply on the column username's value (lower, upper, length) raises such an error. I searched for some similar cases in the pgsql-sql archive but nothing really similar. Any idea? Regards, P.S.: I don't have this problem on other tables containing less data. -- Daniel CAUNE Ubisoft Online Technology (514) 490 2040 ext. 3613 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Local variable and column name conflict
Hi, Is there a way to specify a local variable/parameter within a query where a column has the same name than the local variable/parameter? Example: CREATE OR REPLACE FUNCTION foo(i IN int) RETURNS void AS $$ BEGIN UPDATE bar SET i = i; // column i = parameter i END; $$ LANGUAGE PLPGSQL; For instance, Ingres suggests prefixing local variable/parameter with : in the query: UPDATE bar SET i = :i; I can continue using the de facto Oracle's naming convention (P_parameter and V_local_variable) anyway. That's not a big deal. Regards, -- Daniel CAUNE Ubisoft Online Technology (514) 490 2040 ext. 3613 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Constraint UNIQUE on a column not case sensitive
Hi,
I would like to find an efficient solution for adding/implementing a constraint
UNIQUE on a VARCHAR column not case sensitive:
ALTER TABLE MyTable
ADD CONSTRAINT UNQ_MyTable_MyColumn
UNIQUE (lower(MyColumn)); -- invalid syntax
The idea is to have an index on that column, in a not case sensitive form, i.e.
lower(MyColumn).
SELECT *
FROM MyTable
WHERE lower(MyColumn) = lower('...');
I don't know how to add such a constraint on MyTable except by defining a
trigger on INSERT clause and checking whether lower(:NEW.MyColumn) has been
already inserted in MyTable.
Is there better and more efficient way to do that?
Regards,
--
Daniel
---(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: [SQL] Constraint UNIQUE on a column not case sensitive
-Message d'origine- De : Michael Glaesemann [mailto:[EMAIL PROTECTED] Envoyé : samedi 1 juillet 2006 10:01 À : Daniel CAUNE Cc : pgsql-sql@postgresql.org Objet : Re: [SQL] Constraint UNIQUE on a column not case sensitive On Jul 1, 2006, at 22:47 , Daniel CAUNE wrote: The idea is to have an index on that column, in a not case sensitive form, i.e. lower(MyColumn). I think you're really close. Try CREATE UNIQUE INDEX ci_mycolumn_idx ON mytable(lower(mycolumn)); Does that do what you're looking for? Michael Glaesemann grzm seespotcode net Yes, indeed! I'm stupid! I was searching a constraint form while the creation of an UNIQUE index makes the job too! The following form is not valid: ALTER TABLE MyTable ADD CONSTRAINT UNQ_MyTable_MyColumn UNIQUE (lower(MyColumn)); But your form makes the expected job: CREATE UNIQUE INDEX IDX_MyTable_MyColumn ON MyTable (lower(MyColumn)); Thanks, -- Daniel CAUNE ---(end of broadcast)--- TIP 1: 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
[SQL] Documentation Generator for pl/pgsql
Hi, Are you aware of any documentation generator for PL/PgSQL? Im used to write function documentation using a javadoc style. Im aware of a tool plsqldoc that generated documentation for PL/SQL code, whose syntax is closed to PL/PgSQL. Does someone use this tool for PL/PgSQL? Thanks, -- Daniel CAUNE Ubisoft Online Technology (514) 490 2040 ext. 3613
Re: [SQL] keeping last 30 entries of a log table
insert into log (account_id, message) values (1, 'this is a test); delete from log where account_id = 1 and id not in ( select id from log where account_id = 1 order by timestamp desc limit 30); I'm wondering if there is a more performance oriented method of doing the delete that I'm not thinking of. Depending on whether id is a kind of auto-incremented column that never cycles, I would suggest something like: DELETE FROM log WHERE account_id = 1 AND id ( SELECT MIN(id) FROM log WHERE account_id = 1 ORDER BY timestamp DESC LIMIT 30); I think there will be a performance difference with your method when the number of records to be deleted is huge. Thanks Daniel, I'll try and benchmark them both and see if turns out to be faster than NOT IN. I guess there's no way to get around the subselect though. Column id should be indexed indeed. Anyway, I'm not sure about any performance improvement using that last method, as the most consuming operation might be the DELETE operation, not really the SELECT operation, when dealing with a huge volume of data. -- Daniel ---(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: [SQL] keeping last 30 entries of a log table
I need to write a function which inserts a log entry in a log table and only keeps the last 30 records. I was thinking of using a subquery similar to the following: insert into log (account_id, message) values (1, 'this is a test); delete from log where account_id = 1 and id not in ( select id from log where account_id = 1 order by timestamp desc limit 30); I'm wondering if there is a more performance oriented method of doing the delete that I'm not thinking of. Depending on whether id is a kind of auto-incremented column that never cycles, I would suggest something like: DELETE FROM log WHERE account_id = 1 AND id ( SELECT MIN(id) FROM log WHERE account_id = 1 ORDER BY timestamp DESC LIMIT 30); I think there will be a performance difference with your method when the number of records to be deleted is huge. -- Daniel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] could not open file issue
De : Alvaro Herrera [mailto:[EMAIL PROTECTED] Daniel Caune wrote: Hi, Is there any way to solve the following issue without dropping the table? select count(*) from eventplayerleaveroom; ERROR: could not access status of transaction 3164404766 DETAIL: could not open file pg_clog/0BC9: No such file or directory Are the files in pg_clog close to the vicinity of 0BC9? I don't have any skill in PostgreSQL administration. However I took a look at /var/lib/postgresql/8.1/main/pg_clog and there is no file close to the vicinity of 0BC9 ; the last file in that directory is: -rw--- 1 postgres postgres 221184 2006-04-24 19:27 00C9 A bit far from 0BC9... Do you have any diagnostic? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] could not open file issue
De : Alvaro Herrera [mailto:[EMAIL PROTECTED] Daniel Caune wrote: De : Alvaro Herrera [mailto:[EMAIL PROTECTED] Daniel Caune wrote: select count(*) from eventplayerleaveroom; ERROR: could not access status of transaction 3164404766 DETAIL: could not open file pg_clog/0BC9: No such file or directory Are the files in pg_clog close to the vicinity of 0BC9? I don't have any skill in PostgreSQL administration. However I took a look at /var/lib/postgresql/8.1/main/pg_clog and there is no file close to the vicinity of 0BC9 ; the last file in that directory is: -rw--- 1 postgres postgres 221184 2006-04-24 19:27 00C9 A bit far from 0BC9... Do you have any diagnostic? I'd say you have a corrupted table. How corrupted I don't know. You could try extracting a portion of the table, playing with LIMIT/OFFSET to find out the exact records that are corrupted. Yes, I tried playing with the LIMIT clause, and LIMIT 90 is the better I can pass... :-( -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] could not open file issue
De : [EMAIL PROTECTED] [mailto:pgsql-sql- [EMAIL PROTECTED] De la part de Andrew Sullivan On Mon, Apr 24, 2006 at 12:17:07PM -0400, Daniel Caune wrote: Hi, Is there any way to solve the following issue without dropping the table? I doubt you'll be able to drop the table. I think you have some sort of corruption. Assuming your hardware is good, you maybe oughta take this over to -general to see if the wizards can identify your problem. (But check your hardware first.) It seems that was possible: I tried first to truncate the table (it passed), and finally I tried to drop the table (it also passed). Then I created the table. I'm not sure that it fixes my problem. I modified my fstab file so that Linux checks my file system's health on the next boot. -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 1: 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] Index on nullable column
Is an index on a nullable column useful for retrieving rows having that column null? Nope, because IS NULL isn't an indexable operator. You can make an end-run around that with a partial index, eg create index fooi on foo(f1) where f1 is null This can be used to satisfy queries using where f1 is null, but it's not any good for any other purpose. If you often do where f1 is null and something-about-f2, you might get better mileage with create index fooi on foo(f2) where f1 is null but it's still a very specialized index. Thanks Tom. I will use such an index even if it's very specialized; OLAP world is somewhat full of specialized index anyway... :-) -- Daniel ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Index on nullable column
Hi, Is an index on a nullable column useful for retrieving rows having that column null? SELECT PlayerID FROM PlayerLoginSession WHERE EndTime IS NULL; Regards, -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] OUT parameter
Owen Jacobson [EMAIL PROTECTED] writes: I'd say there's no problem with this, PROVIDED you can ensure you'll never abort before completing the computation. Not really an issue in Postgres: we do not support pass-by-reference parameters and are unlikely to start doing so. There isn't any way that you can affect locals of a calling procedure before you return. Then I've misunderstood the semantics of OUT and more importantly INOUT parameters. Thanks for the correction; I'm reading Daniel Caune's notes on the docs now. -Owen Funny! I started that thread on OUT parameter; that's a kind of circle... :-) -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418 ---(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
[SQL] Custom type
Hi, How can I enter description for my custom types? \dT provides information such as schema, name, and description for all the registered types and custom types. I would like to provide a description for each custom type I create. Thanks, -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Custom type
Hi,
How can I enter description for my custom types?
\dT provides information such as schema, name, and description for
all
the registered types and custom types. I would like to provide a
description for each custom type I create.
Thanks,
--
Daniel CAUNE
Ubisoft Online Technology
(514) 4090 2040 ext. 5418
Daniel,
From the \h command in psql:
rnd=# \h comment
Command: COMMENT
Description: define or change the comment of an object
Syntax:
COMMENT ON
{
TABLE object_name |
COLUMN table_name.column_name |
AGGREGATE agg_name (agg_type) |
CONSTRAINT constraint_name ON table_name |
DATABASE object_name |
DOMAIN object_name |
FUNCTION func_name (arg1_type, arg2_type, ...) |
INDEX object_name |
OPERATOR op (leftoperand_type, rightoperand_type) |
RULE rule_name ON table_name |
SCHEMA object_name |
SEQUENCE object_name |
TRIGGER trigger_name ON table_name |
TYPE object_name |
VIEW object_name
} IS 'text'
I believe this is what you need.
HTH.
I see! I was searching an option in the custom type creation statement,
something like:
CREATE TYPE foo (
...
)
DESCRIPTION something that might be useful;
Thanks for this information!
--
Daniel
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[SQL] OUT parameter
Hi, Is there any suggestion against using OUT parameter for local calculation such as using a local variable? CREATE OR REPLACE FUNCTION foo(a IN int, b1 OUT int, b2 OUT int) AS $$ BEGIN FOR (...) LOOP b1 = (...); b2 = (...); END LOOP; END; $$ LANGUAGE PLPGSQL; or for some reasons (performance or whatever other details of implementation), would it be preferable to use local variable and to initialize the OUT parameters at the end? CREATE OR REPLACE FUNCTION foo(a IN int, b1 OUT int, b2 OUT int) AS $$ V_b1 int; V_b2 int; BEGIN FOR (...) LOOP V_b1 = (...); V_b2 = (...); END LOOP; b1 = V_b1; b2 = V_b2; END; $$ LANGUAGE PLPGSQL; Thanks, -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418 ---(end of broadcast)--- TIP 1: 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] Power cut and performance problem
BTW, I didn't complete my first thought above, which was to ask when you last vacuumed the DB, but then I saw that you were running autovac, so that wasn't likely the problem. BTW, if the problem is actually a raid array that is rebuilding, it should be (hopefullY) fixed by tomorrow morning. An administrator is checking the raid status this morning. Anyway, I did some tests and it seems that some results are weird. For example, the execution of the following query is fast as it used to be (gslog_event_id is the primary key on gslog_event): select max(gslog_event_id) from gslog_event; (= Time: 0.773 ms) while the following query is really slow (several minutes): select min(gslog_event_id) from gslog_event; (index on the primary key is taken) I'm not a hardware expert at all, but I supposed that the whole performance would be degraded when a problem occurs with RAID disks. Am I wrong? Could it be something else? Are there some tools that check the state of a PostgreSQL database? -- Daniel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Power cut and performance problem
For example, the execution of the following query is fast as it used to be (gslog_event_id is the primary key on gslog_event): select max(gslog_event_id) from gslog_event; (= Time: 0.773 ms) while the following query is really slow (several minutes): select min(gslog_event_id) from gslog_event; (index on the primary key is taken) I'm not a hardware expert at all, but I supposed that the whole performance would be degraded when a problem occurs with RAID disks. Am I wrong? Could it be something else? Are there some tools that check the state of a PostgreSQL database? You would be correct, a hardware problem should manifest itself on both those queries. What is the explain analyze output of those two queries? It's possible you have a corrupt index on gslog_event. If that's the case, a reindex would likely remedy the problem. Is postgres logging any errors? The UNIX administrator confirms that this is not a RAID problem. I truncate my table. This is not the most efficient way, but it's okay because this is a data stage table. It seems that it fixes my performance problem. As you said, perhaps the problem was more related to index corruption. Truncating data and inserting new data recreate the index and therefore fix the problem. Thanks, -- Daniel ---(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
[SQL] Power cut and performance problem
Hi, We had a power cut lastly and it seems that our PostgreSQL database suffers from performance since. For example, a simple query such as SELECT MIN(a-primary-key-column) FROM a-table takes quite a very long time; actually I gave up before getting the result. I shutdown and started up the database, and I took at the log file; I don't see any fatal error: LOG: database system was interrupted at 2006-03-20 22:20:22 GMT LOG: checkpoint record is at 10C/14919ED4 LOG: redo record is at 10C/1487E270; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 166159120; next OID: 41575 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 10C/1487E270 LOG: incomplete startup packet FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up LOG: record with zero length at 10C/14CF39F0 LOG: redo done at 10C/14CF39B4 LOG: database system is ready LOG: transaction ID wrap limit is 2147484146, limited by database postgres LOG: incomplete startup packet LOG: received fast shutdown request LOG: shutting down LOG: database system is shut down LOG: database system was shut down at 2006-03-20 22:30:09 GMT LOG: checkpoint record is at 10C/14CF3A34 LOG: redo record is at 10C/14CF3A34; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 166159788; next OID: 41575 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system is ready LOG: transaction ID wrap limit is 2147484146, limited by database postgres LOG: incomplete startup packet LOG: autovacuum: processing database postgres LOG: incomplete startup packet LOG: received fast shutdown request LOG: shutting down LOG: database system is shut down LOG: database system was shut down at 2006-03-20 22:31:24 GMT LOG: checkpoint record is at 10C/14CF3A78 LOG: redo record is at 10C/14CF3A78; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 166159796; next OID: 41575 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system is ready LOG: transaction ID wrap limit is 2147484146, limited by database postgres LOG: autovacuum: processing database postgres LOG: incomplete startup packet Where can I check, please? Is it more likely a hardware problem (the machine seems ok, no error detected)? Regards, -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Power cut and performance problem
I see you're running autovacuum. What's your disk subsytem look like? By chance is it sitting on a RAID 5 that's running in degraded mode right now while it scrubs? Yes, that should be the problem. I will check that tomorrow morning with a Linux administrator. Thanks. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Update counter when row SELECT'd ... ?
I have a simple table: name, url, counter I want to be able to do: SELECT * FROM table ORDER BY counter limit 5; But, I want counter to be incremented by 1 *if* the row is included in that 5 ... so that those 5 basically move to the bottom of the list, and the next 5 come up ... I've checked CREATE TRIGGER, and that doesn't work 'ON SELECT' ... is there anything that I *can* do, other then fire back an UPDATE based on the records I've received? Thanks ... You mean that you want to write a SELECT statement that returns the 5 first rows that have the smallest counter, and just after what, you would like to increment their counter, right? I was thinking of using a table function, something like (I didn't test it): CREATE OR REPLACE FUNCTION my_first_url(P_Limit IN int) RETURNS SETOF table AS $$ BEGIN FOR V_Record IN SELECT * FROM table ORDER BY counter LIMIT P_Limit LOOP UPDATE table SET counter = counter + 1 WHERE name = V_Record.name /* AND url = V_Record.url */; -- if needed RETURN V_Record; END LOOP; END; $$ LANGUAGE PLPGSQL; -- Daniel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] help with function
Hello, I have 2 tables where each table has a column named comments and the tables are related as a one to many. I want to concatenate all the comments of the many side to the one side so I wrote the following plpgsql function to do so. pre CREATE OR REPLACE FUNCTION fixcomments() RETURNS int4 AS $BODY$ DECLARE mviews RECORD; i int4; BEGIN FOR mviews IN SELECT * FROM saleorder WHERE comments is not null and comments '' LOOP -- Now mviews has one record from saleorder EXECUTE 'UPDATE sale SET comments = ' || quote_ident(sale.comments) || quote_ident(mviews.comments) || ' WHERE sale.id = ' || quote_ident(mviews.sale_id); EXECUTE 'UPDATE sale SET comments = ''' || quote_ident(sale.comments || mviews.comment) || ''' WHERE sale.id = ''' || quote_ident(mviews.sale_id) || ; Does that help? i := i + 1; END LOOP; RETURN i; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; /pre I have the following error when I run this code: pre ERROR: missing FROM-clause entry for table sale CONTEXT: SQL statement SELECT 'UPDATE sale SET comments = ' || quote_ident(sale.comments) || quote_ident( $1 ) || ' WHERE sale.id = ' || quote_ident( $2 ) PL/pgSQL function fixcomments line 11 at execute statement /pre Doesn anybody know what I am doing wrong here ? Lacou. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(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: [SQL] Ask a PostgreSql question (about select )
Objet : Re: [SQL] Ask a PostgreSql question (about select ) Please post questions to the list. I'm forwarding this to the SQL list, as I think it's probably most applicable. I don't know much about the Oracle syntax you've used. Hopefully someone will be able to help you. On Mar 13, 2006, at 12:30 , min wrote: Please help me one PostgreSQL Statement, Thanks in Oracle select rownum,groupid,qty from abc --- --- 1 a5 3 2 a2 4 3 a3 5 4 5 . . . in PostgreSql How to wirte Statement ( Rownum - change ??) PostgreSQL doesn't have the Oracle ROWNUM feature. But if you were using ROWNUM to limit a result set, you are more likely to use the LIMIT feature in PostgreSQL: http://archives.postgresql.org/pgsql-sql/2005-05/msg00127.php -- Daniel ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] connectby documentation
Hi, I'm searching for an Oracle START WITH ... CONNECT BY PRIOR ... equivalence. It seems that PostgreSQL (version = 7.4) supports a function connectby that provides similar feature. Unfortunately I don't find any documentation on that function. Could you please give me a link on such documentation? Note: Just an example of the Oracle START WITH ... CONNECT BY PRIOR ... behaviour. 4| || 2| / \ | 1 3 | Hierarchy dependency order / \ | | 5 | 6 | |/ | 7V SELECT JobId, JobParentId FROM JobDependency START WITH JobParentId IN ( _Root_Datamarts_ ) CONNECT BY PRIOR JobId = JobParentId JOBID JOBPARENTID - --- 2 4 1 2 5 1 7 1 3 2 6 3 7 6 Regards, -- Daniel ---(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: [SQL] connectby documentation
-Message d'origine- De : Michael Fuhr [mailto:[EMAIL PROTECTED] Envoyé : lundi, mars 13, 2006 11:12 À : Daniel Caune Cc : postgresql sql list Objet : Re: [SQL] connectby documentation On Mon, Mar 13, 2006 at 10:37:37AM -0500, Daniel Caune wrote: I'm searching for an Oracle START WITH ... CONNECT BY PRIOR ... equivalence. It seems that PostgreSQL (version = 7.4) supports a function connectby that provides similar feature. Unfortunately I don't find any documentation on that function. Could you please give me a link on such documentation? connectby() is part of the contrib/tablefunc module. You'll need to install that module and load it into your database. Somebody has made a CONNECT BY patch but the developers have objected to it for various reasons. Search the list archives for discussion. That sounds good. I tried to install PostgreSQL contrib modules on my Linux/Debian distribution: apt-get install postgresql-contrib (...) The following extra packages will be installed: libpq3 libxml2 postgresql postgresql-7.4 postgresql-client postgresql-client-7.4 postgresql-contrib-7.4 (...) 7.4?! Huh... Is there any sources.list a bit more updated? Where can I download PostgreSQL contrib modules. The documentation 8.1 doesn't help so much. Where can I find more documentation on available contrib. modules? Thanks, -- Daniel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] connectby documentation
outdated packets is unfortunately a big issue on Debian. If you want to have up-to-date apt-packages try www.backports.org Add one of the mirrors from the list to your sources.list, then run apt-get update and then try to install again ... :-) And you'll see, that you can install newer versions than 7.4 :-) Sorry, this is not my day: apt-get install postgresql-contrib-8.1 works fine... Just a link on the documentation that fully explains how connectby() works would be great! :-) Thanks, -- Daniel ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] connectby documentation
Sorry, this is not my day: apt-get install postgresql-contrib-8.1 works fine... Just a link on the documentation that fully explains how connectby() works would be great! :-) The contrib package should have installed a file named README.tablefunc. You are right. The documentation is located in /usr/share/doc/postgresql-contrib-8.1/ . Wow, that was the quest for the Holy Grail! :-) -- Daniel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] connectby documentation
Sorry, this is not my day: apt-get install postgresql-contrib-8.1 works fine... Just a link on the documentation that fully explains how connectby() works would be great! :-) The contrib package should have installed a file named README.tablefunc. You are right. The documentation is located in /usr/share/doc/postgresql-contrib-8.1/ . Wow, that was the quest for the Holy Grail! :-) Huh... It seems that installing the package postgresql-contrib does not make the work itself. I provide hereafter a description about how to install the function connectby (I didn't find such documentation and I don't know where to write this documentation): apt-get install postgresql-contrib-8.1 emacs /usr/share/postgresql/8.1/contrib/tablefunc.sql Replace $libdir by /usr/lib/postgresql/8.1/lib/tablefunc.so (I suggest to modifying only a copy of this file). su postgres psql -f /usr/share/postgresql/8.1/contrib/tablefunc.sql database You can check that the work is done as follows: psql database database = \df connectby List of functions Schema | Name| Result data type | Argument data types +---+--+ - public | connectby | setof record | text, text, text, text, integer public | connectby | setof record | text, text, text, text, integer, text public | connectby | setof record | text, text, text, text, text, integer, text I hope that will help another PostgreSQL newbie. -- Daniel ---(end of broadcast)--- TIP 1: 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] connectby documentation
-Message d'origine- De : Michael Fuhr [mailto:[EMAIL PROTECTED] Envoyé : lundi, mars 13, 2006 19:26 À : Daniel Caune Cc : [EMAIL PROTECTED]; postgresql sql list Objet : Re: [SQL] connectby documentation On Mon, Mar 13, 2006 at 06:38:14PM -0500, Daniel Caune wrote: I provide hereafter a description about how to install the function connectby (I didn't find such documentation and I don't know where to write this documentation): README.tablefunc contains instructions on how to load the module into a database. apt-get install postgresql-contrib-8.1 emacs /usr/share/postgresql/8.1/contrib/tablefunc.sql Replace $libdir by /usr/lib/postgresql/8.1/lib/tablefunc.so (I suggest to modifying only a copy of this file). That shouldn't be necessary unless the package installed the shared objects somewhere other than where the database was expecting. What's the output of pg_config --libdir --version? /usr/lib PostgreSQL 8.1.2 su postgres psql -f /usr/share/postgresql/8.1/contrib/tablefunc.sql database Or, omitting the su, psql -U postgres -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] pgsql aggregate: conditional max
Hi, I need a special aggregation function. For instance, given the following table data: aid| cat | weight --+-+- a1 | Drama | 1 a1 | Romance | 6 a1 | Short | 1 a1 | Other | 7 a2 | Comedy | 1 a2 | Drama | 2 a3 | Drama | 1 a3 | Adult | 2 a3 | Comedy | 1 a3 | Other | 1 I want to group by aid and choose the category (i.e., cat) with the largest weight: aid | max_weighted_cat +- a1 | Other a2 | Drama a3 | Adult Any ideas? Thank you! :) SELECT aid, cat FROM table, ( SELECT aid, max(weight) as weight FROM table GROUP BY aid) AS tablemaxweight WHERE table.aid = tablemaxweight.aid AND table.weight = tablemaxweight.aid; There is a limit case you don't specify how to deal with, when two or more categories have the same maximum weight. The query I wrote retrieves all the categories that have the maximum weight, but perhaps you just want one per aid. -- Daniel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] plsql / time statement
Hi, Is there any option to set so that psql provides the execution time of each SQL statement executed? Regards, -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418
Re: [SQL] How to force PostgreSQL using an index
-Message d'origine- De : Tom Lane [mailto:[EMAIL PROTECTED] Envoyé : mercredi, février 15, 2006 17:47 À : Daniel Caune Cc : Andrew Sullivan; pgsql-sql@postgresql.org Objet : Re: [SQL] How to force PostgreSQL using an index Daniel Caune [EMAIL PROTECTED] writes: SELECT some-columns FROM GSLOG_EVENT WHERE EVENT_NAME = 'player-status-update' AND EVENT_DATE_CREATED = start-time AND EVENT_DATE_CREATED end-time I have an index on EVENT_DATE_CREATED that does it job. But I though that I can help my favourite PostgreSQL if I create a composite index on EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED is more dense that EVENT_NAME). Wrong ... should be EVENT_NAME first. Think about the sort order of the data to see why --- your query represents a contiguous subset of the index if EVENT_NAME is first, but not if EVENT_DATE_CREATED is first. regards, tom lane Yes, you're right! ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] How to force PostgreSQL using an index
Hi, Is there a way to force PostgreSQL using an index for a SELECT statement? I just want to confirm that the index PostgreSQL decides to use is better than the index I supposed PostgreSQL would use (I already analyze the table). Regards, -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418
Re: [SQL] How to force PostgreSQL using an index
On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote: Hi, Is there a way to force PostgreSQL using an index for a SELECT statement? I just want to confirm that the index PostgreSQL decides to use is better than the index I supposed PostgreSQL would use (I already analyze the table). Your best bet is to do set enable_indexscan=false; and then do the EXPLAIN ANALYSE for your select. You might also find that fiddling with other settings affects the planner's idea of what would be a good plan. The planner is sensitive to what it thinks it knows about your environment. I see, but that doesn't explain whether it is possible to specify the index to use. It seems that those options just force PostgreSQL using another plan. For example, I have a table that contains historical data from which I try to get a subset for a specified period of time: SELECT some-columns FROM GSLOG_EVENT WHERE EVENT_NAME = 'player-status-update' AND EVENT_DATE_CREATED = start-time AND EVENT_DATE_CREATED end-time I have an index on EVENT_DATE_CREATED that does it job. But I though that I can help my favourite PostgreSQL if I create a composite index on EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED is more dense that EVENT_NAME). PostgreSQL prefer the simple index rather than the composite index (for I/O consideration, I suppose). I wanted to know how bad the composite index would be if it was used (the estimate cost). Daniel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How to force PostgreSQL using an index
Is there a way to force PostgreSQL using an index for a SELECT statement? Your best bet is to do set enable_indexscan=false; and then do the EXPLAIN ANALYSE for your select. I see, but that doesn't explain whether it is possible to specify the index to use. It seems that those options just force PostgreSQL using another plan. (snip) I have an index on EVENT_DATE_CREATED that does it job. But I though that I can help my favourite PostgreSQL if I create a composite index on EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED is more dense that EVENT_NAME). PostgreSQL prefer the simple index rather than the composite index (for I/O consideration, I suppose). I wanted to know how bad the composite index would be if it was used (the estimate cost). Drop the simple index and re-create it when you're done? Yes, that is a solution! I will try that! :-) As I understand it, the problem with letting clients specify which indexes to use is that they tend, on the whole, to be wrong about what's most efficient, so it's a feature almost specifically designed for shooting yourself in the foot with. I agree that it'd be useful for experimenting with indexing schemes, but then, so is DROP INDEX. Yes, indeed, such a feature could be badly used. However it may happen sometimes that the planner is wrong; I already encountered such situations with both Oracle 9i and SQL Server 2000, even with statistics calculated. That is rare but that happens. Such options /*+ HINT */ or WITH(INDEX(...)) help in such situations, even if that really sucks for the reason you know. Daniel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to force PostgreSQL using an index
Owen Jacobson [EMAIL PROTECTED] writes: On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote: I see, but that doesn't explain whether it is possible to specify the index to use. It seems that those options just force PostgreSQL using another plan. Drop the simple index and re-create it when you're done? BTW, the cute way to do that is BEGIN; DROP INDEX unwanted; EXPLAIN ANALYZE whatever...; ROLLBACK; No need to actually rebuild the index when you are done. This does hold an exclusive lock on the table for the duration of your experiment, so maybe not such a good idea in a live environment ... but then again, dropping useful indexes in a live environment isn't a good idea either, and this at least reduces the duration of the experiment by a good deal. Thanks, that's great! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Does PostgreSQL support job?
I'm not sure to understand. Why calling a function from a script is different from executing a series of SQL commands? I mean, I can run a script defined as follows: SELECT myjob(); where myjob is a stored procedure such as: CREATE OR REPLACE FUNCTION myjob() RETURNS void AS $$ a-lot-of-complex-stuff-here END; $$ LANGUAGE PLPGSQL; Does that make sense? It does make sense if myjob() does more than just execute a bunch of statements, e. G. it contains if(), loops or something else. PLPGSQL is turing complete, plain SQL is not. Yes, indeed, that was the idea! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Does PostgreSQL support job?
Daniel Caune wrote: I'm not sure to understand. Why calling a function from a script is different from executing a series of SQL commands? [snip] Does that make sense? It does make sense if myjob() does more than just execute a bunch of statements, e. G. it contains if(), loops or something else. PLPGSQL is turing complete, plain SQL is not. Yes, indeed, that was the idea! There's another reason: For updating the cron job SQL commands, you need root access (or at least shell access) to the database machine. For updating a stored procedure, you need just the appropriate rights in the database. On larger deployments, this can be an important difference. You are absolutely right. That is such detail I was thinking over. Managing stored procedures into a RDBMS seems less laborious than modifying some SQL scripts on the file system. I mean there is always a need to define initially a script, run by the cron/psql couple, which calls a stored procedure responsible for doing the job (SELECT myjob();). Therefore it is easier to modify implementation details of the job without having to modify the script run by the cron/psql. On another hand, it seems easier to test modification by patching a stored procedure directly in the RDBMS and making some tests on-the-fly. -- Daniel CAUNE ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] CREATE INDEX with order clause
Hi, I would like to create an index on a table, specifying an order clause for one of the columns. CREATE INDEX IDX_GSLOG_EVENT_PLAYER_EVENT_TIME_DESC ON GSLOG_EVENT(PLAYER_USERNAME, EVENT_NAME, EVENT_DATE_CREATED DESC); which is not a valid, as the order clause DESC is not supported. Such as index would improve performance of query like: SELECT GAME_CLIENT_VERSION FROM GSLOG_EVENT WHERE PLAYER_USERNAME = ? AND EVENT_NAME = ? AND EVENT_DATE_CREATED ? ORDER BY EVENT_DATE_CREATED DESC LIMIT 1 Actually, Im not sure that is useful; perhaps PostgreSQL handles pretty well such query using an index such as: CREATE INDEX IDX_GSLOG_EVENT_PLAYER_EVENT_TIME_DESC ON GSLOG_EVENT(PLAYER_USERNAME, EVENT_NAME, EVENT_DATE_CREATED); Any idea? -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418
[SQL] Does PostgreSQL support job?
Hi, I try to find in the documentation whether PostgreSQL supports job, but I miserably failed. Does PostgreSQL support job? If not, what is the mechanism mostly adopted by PostgreSQL administrators for running jobs against PostgreSQL? I was thinking about using cron/plsql/sql-scripts on Linux. Thanks (Tom Lane J) -- Daniel CAUNE
Re: [SQL] Does PostgreSQL support job?
-Message d'origine- De : [EMAIL PROTECTED] [mailto:pgsql-sql- [EMAIL PROTECTED] De la part de Bruce Momjian Envoyé : mercredi, février 01, 2006 17:57 À : Daniel Caune Cc : pgsql-sql@postgresql.org Objet : Re: [SQL] Does PostgreSQL support job? Daniel Caune wrote: Hi, I try to find in the documentation whether PostgreSQL supports job, but I miserably failed. Does PostgreSQL support job? If not, what is the mechanism mostly adopted by PostgreSQL administrators for running jobs against PostgreSQL? I was thinking about using cron/plsql/sql-scripts on Linux. The unix cron systems is what most people use. OK. Thanks. That's fine! ---(end of broadcast)--- TIP 1: 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] Does PostgreSQL support job?
-Message d'origine- De : [EMAIL PROTECTED] [mailto:pgsql-sql- [EMAIL PROTECTED] De la part de Alvaro Herrera Envoyé : mercredi 1 février 2006 19:28 À : Daniel Caune Cc : Owen Jacobson; pgsql-sql@postgresql.org Objet : Re: [SQL] Does PostgreSQL support job? Daniel Caune wrote: Yes, that's it. A job is a task, i.e. set of statements, which is scheduled to run against a RDBMS at periodical times. Some RDBMS, such as SQL Server ..., the current alpha MySQL, ... and Oracle, support that feature, even if such a feature is managed differently from a RDBMS to another. I was amused when I read the MySQL news in LWN.net, because most comments were things like what the hell has this half-baked feature has to do in a RDBMS anyway. http://lwn.net/Articles/167895/ It's true that implementing a job management within an RDBMS is somewhat reinventing the wheel, especially on UNIX systems where cron exists (even on Windows, which supports scheduled tasks). Anyway, job support within a RDBMS sounds more like a facility. While I have built a number of large and small applications with various time-based event scheduling tables stored in an SQL database, including things like triggers that send asynchronous notifications to daemon clients to advise them to re-query for updated schedules, it never in my wildest imaginings occured to me to actually initiate execution autonomously from the database back end. [zblaxell, 2006-01-25, http://lwn.net/Articles/167895/] Well, perhaps zblaxell has only worked on operational systems (OLTP), but such autonomy is sometimes useful in low-cost business intelligence systems (OLAP). -- Daniel CAUNE ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Function with default value?
Hi, Is it possible to define a function with some default values? CREATE OR REPLACE FUNCTION foo(i IN int, j IN int DEFAULT := 1) … Anyway, I didn’t find such a feature described in the PostgreSQL 8.1 documentation, but sometimes, that doesn’t mean that the feature doesn’t exist! J Thanks, Daniel
Re: [SQL] Function with default value?
-Message d'origine- De : Tom Lane [mailto:[EMAIL PROTECTED] Envoyé : dimanche 29 janvier 2006 10:48 À : Daniel CAUNE Cc : pgsql-sql@postgresql.org Objet : Re: [SQL] Function with default value? Daniel CAUNE [EMAIL PROTECTED] writes: Is it possible to define a function with some default values? CREATE OR REPLACE FUNCTION foo(i IN int, j IN int DEFAULT := 1) No. But you can fake many versions of this with a family of functions: CREATE OR REPLACE FUNCTION foo(i IN int, j IN int) ... CREATE OR REPLACE FUNCTION foo(i IN int) ... return foo(i, 1) ... Remember that PG lets you overload a function name by using the same name with different parameter lists. regards, tom lane Yes, thanks Tom, for the second time. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] [HELP] Defining a function as a procedure
Hi, Is there a way to define a function as a procedure, I mean a function that returns nothing. CREATE OR REPLACE FUNCTION foo(in-parameters) AS $$ BEGIN update-some-tables END; $$ LANGUAGE 'plpgsql'; Actually, PostgreSQL complains as a function result type must be specified. I can patch my function so that it compiles but that wont be really nice: CREATE OR REPLACE FUNCTION foo(in-parameters) RETURNS int AS $$ BEGIN update-some-tables RETURN 1; END; $$ LANGUAGE 'plpgsql'; Is there any other prettier way to do that? Thanks, -- Daniel
Re: [SQL] Executing plpgsql scripts using psql, is that possible?
-Message d'origine-
De : [EMAIL PROTECTED] [mailto:pgsql-sql-
[EMAIL PROTECTED] De la part de John DeSoi
Envoyé : lundi 16 janvier 2006 08:51
À : Daniel CAUNE
Cc : pgsql-sql@postgresql.org
Objet : Re: [SQL] Executing plpgsql scripts using psql, is that possible?
On Jan 16, 2006, at 5:35 AM, Daniel CAUNE wrote:
I would like to write some administration plpgsql scripts that
populate some tables (dimension tables) and to execute them using
psql. I’m not sure that is possible with psql as it is with Oracle
sqlplus or SQL Server MSQuery:
If you want to execute a plpgsql function from a file using psql,
just call it with SELECT. So your file might have:
create or replace function my_function(params integer)
returns integer as $$
DECLARE
V_MyObjectID bigint;
BEGIN
V_MyObjectID := RegisterMyObject('a string', 'another string');
AddObjectProperty(V_MyObjectID, 'a string');
AddObjectProperty(V_MyObjectID, 'another string');
END;
$$ language plpgsql;
SELECT my_function(1);
and then psql -f script.sql my_db
Yes, but that requires creating a function while I would prefer not having do
so, as I said in my previous mail: I mean, without creating a function that
wraps the whole, of course! :-). Why? Actually this is not a function; this
is a script that inserts static data into dimension tables such as Country,
Language, etc.
I have several scripts responsible for creating the database and all the
objects (tables, views, constraints, indexes, user-defined functions, etc.) of
my project. I would like to have some other scripts to initialize dimension
tables, i.e. inserting static data in those tables. The idea is to automate
the whole creation and initialization of a database on a PostgreSQL server; I
already have an Ant task that searches for SQL files, orders them, and runs
them against the specified database server. The database and all relative
objects are set up in one step.
So, I completely understand that I can write an SQL script that:
1 - creates a function that wraps SQL code that inserts static data into
dimension tables.
2 - executes that function
3 - destroys that function
But actually that is a bit weird, isn't it?
Thanks,
Daniel
---(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: [SQL] Executing plpgsql scripts using psql, is that possible?
If you want to use plpgsql it will need to be within a function. In your reply you mention creating user-defined functions as part of the set up procedure. It would not be weird to include the static data function as part of that procedure and then call it to load the data. I see no reason to destroy the function after use. If that is not the route you want to take you may want to look at the following for information on using COPY to load data from a file into a table- www.postgresql.org/docs/8.1/interactive/sql-copy.html Yes, COPY may be an interesting option too. Thanks! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Executing plpgsql scripts using psql, is that possible?
Hi,
I would like to write some administration plpgsql scripts that populate some
tables (dimension tables) and to execute them using psql. I’m not sure that is
possible with psql as it is with Oracle sqlplus or SQL Server MSQuery:
Oracle sqlplus:
---
DECLARE
V_MyObjectID bigint;
BEGIN
V_MyObjectID := RegisterMyObject('a string', 'another string');
AddObjectProperty(V_MyObjectID, 'a string');
AddObjectProperty(V_MyObjectID, 'another string');
END;
SQL Server MSQuery:
---
DECLARE @MyObjectID int
SET @MyObjectID = RegisterMyObject('a string', 'another string')
EXECUTE AddObjectProperty(MyObjectID, 'a string');
EXECUTE AddObjectProperty(MyObjectID, 'another string');
Any idea how I can translate such a script for psql? I mean, without creating
a function that wraps the whole, of course! :-)
Thanks,
Daniel
---(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
