Re: [GENERAL] pg_class and relfilenode
> e.g. > pg_shadow_usename_index > pg_stat_activity > pg_stat_database > They are system views and correspond to no file. See PostgreSQL's documents. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] EXECUTE + transaction = unexpected error -8
Hi,
It seems that there is a problem when executing a dynamic commands
containing a transaction...
Here's a simple example...
CREATE OR REPLACE FUNCTION fct_trig2()
RETURNS TRIGGER AS '
BEGIN
EXECUTE ''BEGIN;''
|| ''CREATE TABLE t2_'' || NEW.id::TEXT || ''(''
|| '' CONSTRAINT pkt2_'' || NEW.id::TEXT || '' PRIMARY KEY (id)''
|| '') INHERITS (t1);''
|| ''COMMIT;'';
RETURN NEW;
END;'
LANGUAGE 'plpgsql';
CREATE TABLE t1 (id INT PRIMARY KEY);
CREATE TABLE t2 (id INT PRIMARY KEY);
CREATE TRIGGER trig2 AFTER INSERT ON t2 FOR EACH ROW EXECUTE PROCEDURE
fct_trig2();
test=# INSERT INTO t2 VALUES (1);
ERROR: unexpected error -8 in EXECUTE of query "BEGIN;CREATE TABLE
t2_1( CONSTRAINT pkt2_1 PRIMARY KEY (id)) INHERITS (t1);COMMIT;"
CONTEXT: PL/pgSQL function "fct_trig2" line 2 at execute statement
If I don't use a transaction in the executed statement, there is no problem.
Remark: In this example a transaction is not needed, but in my real
usage of this I'm creating a table and granting permissions, and maybe
also creating a view. So a transaction is preferable.
I've tested this on "PostgreSQL 7.4.1 on i386-pc-linux-gnu, compiled by
GCC i386-linux-gcc (GCC) 3.3.2 (Debian)", Debian Sarge.
And also on "PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC
2.95.4", Debian Woody.
On that system, the function fct_trig2() needs to return OPAQUE instead
of TRIGGER.
Thanks,
Pascal
PS: As I'm not subscribed to this ml, please keep me in the reply.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[GENERAL] Best replication options
What is the best replication option out there today for postgresql. Have been trying to get rserv working and that hasn't been the easiest. Would like to run 7.4.1. Any suggestions? -Steve ---(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] Pg + perl + apache
I'm having some odd issues, but I'm not sure exactly the cause of them, but postgres is the component in the system that is throwing the errors, so I thought I'd start here. I have built a web application that uses postgres (7.3.2) as its backend, with the front being an Apache2/mod_perl solution. I've printed what I think the relevant versions are below. Anway, DBD::Pg throws random errors into my logs, along the lines of what is below (the lines beginning with SQL are debug statements from the script, and are the lines references by the DBD error. SQL: SELECT session_key FROM session WHERE user_id = 101 at /home/dragon/public_html/iactweb/lib/perl/Apache/IACT/AuthUtil.pm line 38. DBD::Pg::db selectrow_array failed: ERROR: current transaction is aborted, queries ignored until end of transaction block at /home/dragon/public_html/iactweb/lib/perl/Apache/IACT/AuthUtil.pm line 40. [Thu Feb 12 18:48:13 2004] [error] [client 127.0.0.1] DBD::Pg::db selectrow_array failed: ERROR: current transaction is aborted, queries ignored until end of transaction block at /home/dragon/public_html/iactweb/lib/perl/Apache/IACT/AuthUtil.pm line 40. with the corresponding postgresql.log entry being LOG: statement: SELECT session_key FROM session WHERE user_id = 101 ERROR: current transaction is aborted, queries ignored until end of transaction block This seems to randomly happen in my application, resulting in an error page being returned to the user. However, if the user refreshes their browser (sometimes more than once), it seems to come back fine. I have seen it happen with DBD::Pg::db st, selectrow_array, selectrow_arrayref, and selectrow_hashref. The components of the system currently are (upgraded this morning to the latest): DBI 1.40 DBD::Pg 1.31 Apache::DBI 0.93 Apache 2.0.48 and 2.0.47 mod_perl 1.99.11 and 1.99.12 PostgreSQL 7.4.1 and 7.3.2 I'm leaning towards blaming the interaction between DBI and Apache::DBI (persists DB connections within apache), but I'd like to rule out postgres as the problem, if anyone can do that conclusively. I never disconnect from my DB handles, since Apache::DBI caches them, so I was wondering about the whole 'ping' method. I wrote a script to exercise one of my scripts from the command line, and ran it 1 times without error, so I am pretty sure that there is some interaction error going on in the system. If anyone has seen anything like this before (i.e. random errors in a similar apache/mod_perl) situation, or might have any pointers as to where to look next, let me know. What would cause postgres to return an error for a selectrow_array, or one of those others mentioned? Thanks -Mike ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Pg + perl + apache
On Sat, Feb 14, 2004 at 04:35:58PM -0500, Michael L. Artz wrote: > I'm having some odd issues, but I'm not sure exactly the cause of them, > but postgres is the component in the system that is throwing the errors, > so I thought I'd start here. > with the corresponding postgresql.log entry being > > LOG: statement: SELECT session_key FROM session WHERE user_id = 101 > ERROR: current transaction is aborted, queries ignored until end of > transaction block This happens when, within a transaction gets an error, you don't notice and keep blindly sending queries. Scroll up to find the actual error. If it's the first transaction in a session, that means someone forgot to clear their transaction. I guess you could fix it then by sending "abort" as the first query. Hope this helps, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > If the Catholic church can survive the printing press, science fiction > will certainly weather the advent of bookwarez. >http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow pgp0.pgp Description: PGP signature
Re: [GENERAL] Confused newbie (to PG not DB's)
On Tue, 10 Feb 2004, mike wrote:
> I have setup a database that I am the owner of, and I am trying to setup
> constraints and it doesn't seem to be working
>
> Example
>
> - Table: public."Invoice_Header"
>
> -- DROP TABLE public."Invoice_Header";
>
> CREATE TABLE public."Invoice_Header"
> (
> "Inv_ID" int4 NOT NULL DEFAULT nextval('public."Invoice
> Header_ID_seq"'::text),
> "client_ID" int2 NOT NULL,
> payable_recievable varchar(5),
> "date_CREATED" date,
> "AMOUNT" money,
> amount_paid money,
> date_paid date
> ) WITH OIDS;
>
> I am trying to make Inv_ID a primary key as follows
>
> alter table Invoice_Header ADD CONSTRAINT PK_inv PRIMARY KEY (Inv_ID)
>
> which runs without error, but nothing changes
Hmm, I don't have 7.3.x to test on here, but that should have errored
unless you double quoted the the table and column name in the alter table.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL License
Nope. PostgreSQL is BSD licensed. On Feb 10, 2004, at 11:40 AM, Artemy wrote: What about the PostgreSQL License. If I use the database for the commercial purpose, Do I have to purchase the license?. P.S. For example MySQL Databse have the commercial license for that. Best Regards Artjom Smekalin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL License
PostgreSQL has BSD license. Tha means do what do you want :) Oleg On Tue, 10 Feb 2004, Artemy wrote: > What about the PostgreSQL License. If I use the database for the commercial purpose, > Do > I have to purchase the license?. > > P.S. For example MySQL Databse have the commercial license for that. > > > Best Regards > Artjom Smekalin > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(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] Pg + perl + apache
"Michael L. Artz" <[EMAIL PROTECTED]> writes: > Anway, DBD::Pg throws random errors into my logs, along the lines of > what is below (the lines beginning with SQL are debug statements from > the script, and are the lines references by the DBD error. > LOG: statement: SELECT session_key FROM session WHERE user_id = 101 > ERROR: current transaction is aborted, queries ignored until end of > transaction block This error cannot occur unless some other error occurred previously in the same transaction. My bet is that you are using some sort of connection-pooling mechanism, and are being sloppy about handing off database connections from one Apache thread to another. Specifically, some thread is getting an error and then letting go of the connection without rolling back the failed transaction on the database side. The next guy who gets that connection from the pool fails as above. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Pg + perl + apache
On Saturday 14 February 2004 22:35, Michael L. Artz wrote: (...) > If anyone has seen anything like this before (i.e. random errors in a > similar apache/mod_perl) situation, or might have any pointers as to > where to look next, let me know. What would cause postgres to return an > error for a selectrow_array, or one of those others mentioned? Just checking, but do all your scripts have : use strict; use warnings; at the top? Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgreSQL Indexing versus MySQL
I had this same problem not long ago, and it caused me go with PostgreSQL over MySQL on a project. The problem you're describing does not exist in PostgreSQL. Creating a new index on a table does not require recreating the other indexes. MySQL also has the same problem if you add or drop columns, all indexes need to be recreated. Again, not a problem in PSQL. You'll find times similar to Oracle and MS SQL (I've never directly compared them, but they feel about the same). Regards, Adam Ruth On Feb 11, 2004, at 1:58 PM, Chris Fossenier wrote: I currently have a client with a database that must hold 125 million records and all tallied about 250 fields. The database has been normalized and indexed appropriately. If any of you have worked with MySQL, you will have discovered that indexing is very limited. You can only have one index file per table. The indexing process actuallly creates a full copy of the original table and once you get above 2 indexes with 125million records, it is extremely slow. Should I even bother trying PostgreSQL to resolve this issue? We can generate the same indexes in MS SQL and Oracle in a fraction of the amount of time when held up to MySQL. Thanks Chris.
Re: [GENERAL] nonblocking libpq large object access?
On 15. feb 2004, at 18:18, Tom Lane wrote: A workaround you could think about is invoking the LO functions via ordinary SELECT commands, ignoring libpq's LO API altogether. This would have been rather painful in pre-7.4 releases since you'd have to be willing to deal with quoting and dequoting "bytea" values, but as of 7.4 you could use PQsendQueryParams() and specify binary format for the bytea inputs and results. I think I'll go down that road provided it's not a performance killer. I'll also be considering using a custom perl-socket-server system I implemented for kicks (it's put up at http://uti.is/david/320_maintserver_procs.sql, BSD license). It does gzip of the steam for free, which is a win. d. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL Indexing versus MySQL
Chris Fossenier wrote: I currently have a client with a database that must hold 125 million records and all tallied about 250 fields. The database has been normalized and indexed appropriately. If any of you have worked with MySQL, you will have discovered that indexing is very limited. You can only have one index file per table. The indexing process actuallly creates a full copy of the original table and once you get above 2 indexes with 125million records, it is extremely slow. Should I even bother trying PostgreSQL to resolve this issue? We can generate the same indexes in MS SQL and Oracle in a fraction of the amount of time when held up to MySQL. This is a known MySQL specific problem. I know of no other database that handles index creation in such an inefficient way. Creating an index in PostgreSQL requires scanning the entire main table and sorting the resulting key set ... don't see how to do it better. Every index is stored in its own (set of) file(s). During index creation, an exclusive lock on the table is required by create index, so reindexing your entire DB is not your preferred operation during peak access times of your webserver. But I think professional DBA's don't torture Oracle that way either. Jan Thanks Chris. -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] making tsearch2 dictionaries
I'm trying to make myself a dictionary for tsearch2 that converts
numbers to their english word equivalents. This seems to be working
great, except that I can't figure out how to make my lexize function
return multiple lexemes. For instance, I'd like "100" to get converted
to {one,hundred}, not {"one hundred"} as is currently happening.
How do I specify the output of the lexize function so that this will
happen?
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] making tsearch2 dictionaries
Okay, so I was actually able to answer this question on my own, in a
manner of speaking. It seems the way to do this is to merely return a
larger char** array, with one element for each word. But I was having
trouble with postgres crashing, because (I think) it tries to free each
element independently before using all of them. I had set each element
to a different null-terminated chunk of the same palloc'd memory
segment. Having never written C stored procs before, I take it that's
bad practice?
Anyway, now that this is working, my next question is: can I take the
lexemes from one dictionary lookup and pipe them into another
dictionary? I see that I can have redundant dictionaries, such that if
lexemes aren't found in one it'll try another, but that's not quite the
same.
For instance, the en_stem dictionary converts "hundred" into "hundr".
Right now, my dictionary converts "100" into "one" and "hundred", but
I'd like it to filter both one and hundred through the en_stem
dictionary to arrive at "one" and "hundr".
It also occurs to me I could pipe things through an ispell dictionary
and be able to handle misspellings
On Sun, 2004-02-15 at 15:35, Ben wrote:
> I'm trying to make myself a dictionary for tsearch2 that converts
> numbers to their english word equivalents. This seems to be working
> great, except that I can't figure out how to make my lexize function
> return multiple lexemes. For instance, I'd like "100" to get converted
> to {one,hundred}, not {"one hundred"} as is currently happening.
>
> How do I specify the output of the lexize function so that this will
> happen?
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
