Re: [GENERAL] item descriptions in psql
> Hi, > > I just found a reference to descriptions to functions/tables/...etc. > and am now wondering how to add them myself? > > Joost Roeleveld not sure if there is a shortcut to this (it's short enough already): INSERT INTO pg_description (objoid, description) SELECT oid, 'your description' FROM pg_class WHERE relname = 'your_table_name'; INSERT INTO pg_description (objoid, description) SELECT oid, 'your description' FROM pg_proc WHERE proname = 'your_procedure_name'; INSERT INTO pg_description (objoid, description) SELECT oid, 'your description' FROM pg_type WHERE typname = 'your_type_name'; INSERT INTO pg_description (objoid, description) SELECT oid, 'your description' FROM pg_operator WHERE oprname = 'your_operator_name'; (in case of operators, oprname is '=', '<=', '>>~', etc.) in older versions (pre-6.3), one had to typecast the names and descriptions: INSERT INTO pg_description (objoid, description) SELECT oid, 'your description'::text FROM pg_type WHERE typname = 'your_type_name'::name; --Gene
[GENERAL] Re: [INTERFACES] Announce: PostgreSQL-6.5.3 binaries available forWindows NT
Tusar wrote: > Does the binary contain sql server with it? Sure. Please run "psql -h hostName template1". > Tusarkanti Nayak > @Communicators > Phone: 91 - 11 - 5535770(O) > Phone: 91 - 11 - 5613992(O) > Phone: 91 - 11 - 5528098(R) > Fax: 91 - 11 - 5613991 > URL: http://tusar.netshooter.com > Mail:<[EMAIL PROTECTED]> > * > ** You never know what is enough until you > you know what is more than enough *** > * > DeVries' Dilemma: > If you hit two keys on the typewriter, the one you don't want > hits the paper. Enjoy! Kevin.
Re: [GENERAL] Index pg_proc_prosrc_index: NUMBER OF INDEX' TUPLES (1071)ISNOT THE SAME AS HEAP' (1070)
Bruce Momjian wrote: > > > Bruce Momjian wrote: > > > > > > Anyone seen this message or know what it means? > > > > > > > > NOTICE: Index pg_proc_prosrc_index: NUMBER OF INDEX' TUPLES (1071) IS > > > > NOT THE SAME AS HEAP' (1070) > > > > > > Drop index and recreate. Next release will be more specific in error > > > message. > > > > I have no idea *which* index to drop/recreate, and I have hundreds of them. > > Ouch. > > That will also be fixed. I thought that the index in question was, in fact, pg_proc_prosrc_index in the above example. If that's the case, then is it possible for Ed to rebuild a system index? The only absolutely surefire way is to dump/reload, isn't it? Maybe somewhere someone is doing a heap_insert(), heap_replace(), et al, and an event is happening which is causing the code to not get to the CatalogOpenIndices()/CatalogIndexInsert()/CatalogCloseIndices()... Just curious, Mike Mascari
Re: [GENERAL] item descriptions in psql
"Gene Selkov, Jr." wrote: > > > Hi, > > > > I just found a reference to descriptions to functions/tables/...etc. > > and am now wondering how to add them myself? > > > > Joost Roeleveld > > not sure if there is a shortcut to this (it's short enough already): > > INSERT INTO pg_description (objoid, description) > SELECT oid, 'your description' FROM pg_class WHERE relname = 'your_table_name'; > > INSERT INTO pg_description (objoid, description) > SELECT oid, 'your description' FROM pg_proc WHERE proname = 'your_procedure_name'; > > INSERT INTO pg_description (objoid, description) > SELECT oid, 'your description' FROM pg_type WHERE typname = 'your_type_name'; > > INSERT INTO pg_description (objoid, description) > SELECT oid, 'your description' FROM pg_operator WHERE oprname = >'your_operator_name'; > > (in case of operators, oprname is '=', '<=', '>>~', etc.) > > in older versions (pre-6.3), one had to typecast the names and descriptions: > > INSERT INTO pg_description (objoid, description) > SELECT oid, 'your description'::text FROM pg_type WHERE typname = >'your_type_name'::name; > > --Gene And also note that pg_dump does not yet dump descriptions. So, until the next release, if your going to document your database schema, be sure to dump pg_description before performing any dump..blow-away..reload sequence. Mike Mascari
[GENERAL] getting user-list
Hi, I would like to know if, and how, I can find out who's logged into the database at any given time. And is it possible to maintain a connection-log of log-ins and log-outs? with kind regards, Joost Roeleveld
[GENERAL] Postgres performance problems.
Hi, We have developed an application using PG 6.5.1, DBI-1.13 and DBD-0.92 with perl5. We have a small db with a query with 5 joins and other smaller singleton selects. Now this query will be executed about 50 times a second. While doing a load test to find performance we find that for 100 parallel perl requests only around 10 postgres processes get executed. Why is this so. Is there anyway to configure and increase this. -N and -B parameters in postmaster don't seem to help. Thanks in Advance, Murali Differentiated Software Solutions K.P.KrishnanPhones:+91-44-4349882 Client Understanding +91-44-4349469 Intercept Consulting India (P) Ltd Fax: +91-44-4349842 Chennai Mobile 9841064540 India email: [EMAIL PROTECTED]
[GENERAL] Interbase replacement
Hi, We use Postgresql, but we also use Interbase. However, at present there is a question mark over the future of Interbase (the top people have all resigned and we do not yet know what inprise will do with the product, there are rumours it will be dropped). Many users of Interbase are currently looking at alternatives. Of these Postgresql is in many ways one of the best options. It lacks only a few things critical (at least for some) - support for Windows 9x - transaction log with roll forward from backups desireable - stored procedures - relational integrity via foreign keys Would it be possible to find ways to pay postgresql developers to add these things? We would be very happy to pay several $1000 for these and it might be possible to find others from the Interbase community. I know that we have discussed Win 9x before and that there are technical issues. I am hoping that with funds ways around these could be found. Dave
Re: [GENERAL] copy command -- foiled by pg_atoi
Folks-- Thanks for the ideas. But bisection just seemed too cumbersome. In the end I decided to write a data filter in perl which checks all the data for valid types before putting it into the DB! Mike Ed Loehr wrote: > > I have found that judicious placement of a few queries (selects, intentional > errors, etc.) within a long sequence of inserts will help segment them for > identification of offending lines. Hokie, but it helps me. ... > Thomas Reinke wrote: > > > I've run into this a few times as well. My strategy to "hunt" down > > the offending line has been to do a "bisection" algorithm. > > > > Jose Soares wrote: > > > > > > This is also my problem. I'm getting '@!?àù§èé+*_|!&/%§¸' to load a > > > table with more than 23,000 rows > > > because I don't know in which line I have to look for the the error.
[GENERAL] creating trigger
Hi all, I'm trying to create a trigger. I created a function which returns a count of the rows. I used this procedure while creating the trigger. But, I get an error stating SQL must return opaque. I tried the same with returning a varchar. I get the same error again. In simple words, I need to create a trigger. Can anyone help me in doing this. BTW, what is similar fxn. as dbms_output.println() in Oracle. Thanks in advance.
Re: [GENERAL] Getting value of SERIAL column after insert from libpq?
Thanks for the help, it works great!
However, there is a problem with performance.
I am moving from MySQL to Postgres, and to test performance I am inserting a
large row (30 fields) into a table from my C program. I am running this
program 50 times, and timing the results. The MySQL version of the program
took 0.75 seconds to execute 50 times, but the Postgres version takes 22-25
seconds. A similar test with a simple select takes 3.5 seconds on Postgres
but 0.8 on MySQL. Postgres undoubtably has more features and is better for
my app than MySQL, but are these performance values normal?
All my program does is read the query from a text file, open the database
connection, perform the query, output currval('seqence_name') or the query
results to a text file, and close the connection. This is how my app needs
to work.
Thanks,
Mark.
>> Hi,
>>
>> I have written a C program to insert a row into a table with a
>> SERIAL column.
>>
>> Is there a way of returning the inserted value for this column
>> to my program? I.e. if there are rows with the serial column
>> for 1,2,3,4 and 5, and I insert a row, my program needs to be
>> told "6" for the new serial. There may be many instances of the
>> program running simultaneously so I can't do a "select max..."
>> or "select last_value..." workaround because by the time the
>> select is done, there may have been other rows inserted so the
>> last_value would be wrong. Also the program needs to be table-name
>> and column-name independent so that it can work for ANY insert
>> query into a table with a SERIAL column.
>
>Answer is that currval('seqence_name') will return your last sequence
>number, even if another session has assigned a sequence number since
>your nextval() call.
Re: [GENERAL] Getting value of SERIAL column after insert from libpq?
Mark Alliban wrote:
>
> Thanks for the help, it works great!
>
> However, there is a problem with performance.
> I am moving from MySQL to Postgres, and to test performance I am inserting a
> large row (30 fields) into a table from my C program. I am running this
> program 50 times, and timing the results. The MySQL version of the program
> took 0.75 seconds to execute 50 times, but the Postgres version takes 22-25
> seconds. A similar test with a simple select takes 3.5 seconds on Postgres
> but 0.8 on MySQL. Postgres undoubtably has more features and is better for
> my app than MySQL, but are these performance values normal?
>
> All my program does is read the query from a text file, open the database
> connection, perform the query, output currval('seqence_name') or the query
> results to a text file, and close the connection. This is how my app needs
> to work.
>
> Thanks,
> Mark.
Are you running with fsync() disabled? That is the single
largest bottleneck for PostgreSQL performance and is
disabled by default in MySQL, except on NT (since NT has a
tendency to crash). You can disable fsync() with the
postmaster -o -F option, to pass the option to the backend.
See the postmaster and postgres man pages for more info.
Hope that helps,
Mike Mascari
