Re: [GENERAL] WAL questions
"Robert B. Easter" <[EMAIL PROTECTED]> writes: > What is there for a user or admin or programmer to know about the new WAL > stuff? Vadim is the man who ought to answer this (and he's on the hook to write a lot of documentation before 7.1 ships ;-)). But my understanding is that as of 7.1, WAL will not really provide any user-level features like audit trails or point-in-time recovery. The only useful thing it does right now is reduce the cost of fsyncs. It provides an infrastructure on which we can build audit trails etc in future releases --- but the superstructure atop this infrastructure ain't there yet. Over to you, Vadim ... regards, tom lane
Re: [GENERAL] Doesn't use index, why?
> In this case the planner is doing *exactly* the right thing; it is > smarter than you are. If you want to prove it, force the planner to > use an indexscan by doing SET ENABLE_SEQSCAN TO OFF. Then time the > query, and compare the runtime against the seqscan version. > > The bottom line here is that a query that needs to touch more than a > few percent of the rows in a table is better off being done as a > seqscan. The only other workaround is to CLUSTER the table on an index, then force an index scan. That _may_ be faster. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] 7.1 PL/pgSQL EXECUTE Command
> EXECUTE ''UPDATE table SET '' || fieldname || > '' = '' || newvalue || '' WHERE ...''; > > None of this stuff is in the docs yet :-(. Seems we've been a tad > sloppy about adding documentation for new features this time around. > Anyone want to submit a documentation patch to cover this stuff? I still need to go through the HISTORY file and make sure all the user-visible stuff is in the docs, unless someone else does this first. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] COPY error: pqReadData() -- backend closed the channel unexpectedly
Lee Joramo <[EMAIL PROTECTED]> writes: > [PostgreSQL 6.5.2 on powerpc-unknown-linux-gnu, compiled by gcc 2.95.2] Hm. Did you compile at -O0? Pre-7.1 PG is known to have a lot of problems on PPC if compiled with any optimization at all. > The 'classifieds.dat' consists of about 2200 lines. I have determined > that the problem is caused by just the following line (literal tabs have > been replaced with ): Are there any lines with more than 2700 characters worth of ad copy? Pre-7.1 PG has a limit of 1/3 page or about 2700 bytes for any indexed column ... and 6.5 tends to fall over rather than give an error if you exceed the limit :-( This particular line is well below that, but you could still see the problem appear or disappear depending on which entries happen to fall on the same disk page, so subtracting a line that isn't directly causing the problem might be enough to mask the bug. If that's not it, I'm not sure ... but I'd still recommend updating to 7.0.3 just on general principles. > I also learned in the archive that when a 'backend closed' > error occurs I should be able to find a 'core' file located in the > database's directory. But I have not found a core file. On many Linuxes, processes started from boot scripts are by default started with "ulimit -c 0", which prevents creation of core files. You may need to say "ulimit -c unlimited" in the postmaster startup script to allow creation of corefiles. regards, tom lane
[GENERAL] DES encryption in Postgres?
I looked through all the docs, and I couldn't find a function which would simply DES encrypt a string. Is DES not implemented in Postgres? Or am I just not finding the function? Thanks
[GENERAL] Advice needed please
Hi all, As I not familiar with the linux newsgroups and mailing lists, can anyone give me some guidance as to where I should post details of a java development job? It's a Java GUI application which interfaces to a PostgreSQL database backend through JDBC. Regards and best wishes, Justin Clift PostgreSQL Database Administrator
[GENERAL] UPDATE in pl/pgsql
Quick question: Is there a way to find out how many rows an UPDATE affected in pl/pgsql? Thanks
[GENERAL] WAL questions
What is there for a user or admin or programmer to know about the new WAL stuff? What all does it do? Does it allow for an audit file to be created, which can be used to playback and/or rewind the transactions on the database by user/admin commands? How do checkpoints limit or affect how far back recovery is possible (if at all)? When is a checkpoint made? Does it allow for online recovery or only offline? What are the settings/parameters that control it (if any, like size of log at which to cut off a new one)? Can the WAL files that are made be read by humans and where are they stored? How transparent is this feature? Maybe I'm confused! I have some familiarity with a mainframe database that made audit tapes of all transactions. A nightly full dump was also made. Kinda old stuff. The tapes were kept for something like 2 weeks before being recycled. If the database crashed, it was possible to restore the database back to any time by using a full dump and some audit tapes. The dumps and audit tapes were specific to a database, not the whole DBMS. Other databases could be up and running normally while one was being rolled back and then forward again after fixing some problem. Even the one being restored could be online, queuing or processing some queries until the recovery was done. Something like that, it was a hospital environment. How does WAL compare to any of this, if at all? Can WAL be described as a deferred fsync of a batch of transactions? -- Robert B. Easter [EMAIL PROTECTED] - -- CompTechNews Message Board http://www.comptechnews.com/ -- -- CompTechServ Tech Services http://www.comptechserv.com/ -- -- http://www.comptechnews.com/~reaster/
[GENERAL] 6.x or 7.x binaries for Solaris 2.5
Does anyone know if there are prebuilt binaries for PostgreSQL 6.x or 7.x for Sparc Solaris 2.5, and where they can be found. Thanks in advance for any assistance. Mike Cianflone
[GENERAL] changing the time interval between checkpoints
how does one change the time intervals between 'checkpoints'?
[GENERAL] Re: database names are all numbers now
On Tue, 9 Jan 2001, Peter Eisentraut wrote: > > after i ran vacuum analyze, i see that all my database directories with > > respect to their database names have changed to all numbers: > > > is this normal? > > Yes, and it surely was like that before the vacuum analyze as well. This > is new with 7.1. how would one now use symblinks to use another drive for one of the larger files? this is what i did with mysql before.
[GENERAL] COPY error: pqReadData() -- backend closed the channel unexpectedly
I am running to the following error when copying a file to a table: "backend closed the channel unexpectedly" My basic system info: [PostgreSQL 6.5.2 on powerpc-unknown-linux-gnu, compiled by gcc 2.95.2] The Table: able= classifieds +--+- -+---+ | Field | Type| Length| +--+- -+---+ | category_number | int4 | 4 | | bullet | char() | 1 | | border | bool | 1 | | image| varchar()| 32 | | rmc | bool | 1 | | adcopy | text | var | +--+- -+---+ Indices: classifieds_adcopy classifieds_bullet classifieds_category_number classifieds_image A copy of the psql command and error message: rmcars2=> DELETE FROM classifieds; rmcars2=> COPY classifieds FROM '/path/classifieds.dat'; pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. We have lost the connection to the backend, so further processing is impossible. Terminating. The 'classifieds.dat' consists of about 2200 lines. I have determined that the problem is caused by just the following line (literal tabs have been replaced with ): 825ffNeed more growing room ? Cozy up by one of 2 fireplaces, and stay warm this winter! This beautifully maintained rancher, in paradise hills, offers 3 bedrooms, 2 baths, dining and family rooms, and a large eat-in kitchen loaded with cupboards. 1844 square feet of comfortable living on a large corner lot make this the perfect home for the growing family. All these amenities and more at an affordable price of only $144,900. Listing#00-5968 Call Shirley McGuiness 255-3810 or 254-8074 Keller Williams Realty Additional information: The 'classifieds.dat' file is generated from our classified ad management system. I am pulling this data into postgre for publication on the web. The 'classifieds.dat' files generate from two previous weeks work just fine. (And still do!) If I remove the offending line form the file, the COPY command works just fine. After I isolated the line causing the problems, I assumed that I would quickly find the source of the problem, but I have not. Here is what I have done to isolated the problem: * I inspected the line for wayward quotes or escape characters. none found * I have tried truncating the line until it work. Results were strangely inconsistent * I substituted letters for punctuation. no effect * substituted letters and numbers with the letter 'a' It worked! * substituted spaces with '_' It worked! (I don't understand this!) The above where each performed on a copy of the original bad line. I have searched the mailing list archive, I did find number of messages regarding the same error message, but none that seem to apply to my situation. I also learned in the archive that when a 'backend closed' error occurs I should be able to find a 'core' file located in the database's directory. But I have not found a core file. thanks -- Lee A. Joramo [EMAIL PROTECTED] The Nickel Want Adswww.nickads.com Internet Manager 970-242-
Re: [GENERAL] database names are all numbers now
Thomas T. Thai writes: > after i ran vacuum analyze, i see that all my database directories with > respect to their database names have changed to all numbers: > is this normal? Yes, and it surely was like that before the vacuum analyze as well. This is new with 7.1. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [GENERAL] pgsql & mnogosearch
mnogosearch only does inserts into the database, it doesn't check for a
previous occurance of the record first, so you are getting what is
expected ...
I think they work under teh guise that better return an error then do two
queries ...
On Mon, 8 Jan 2001, Thomas T. Thai wrote:
> i'm starting to use mnogosearch 3.1.8 and pgsql-cvs on
> NetBSD/Alpha.
>
> i'm getting a tremendous amount of these errors in my log file when i'm
> running indexer:
> ...
> ERROR: Cannot insert a duplicate key into unique index url_url
> ERROR: Cannot insert a duplicate key into unique index url_url
> ERROR: Cannot insert a duplicate key into unique index url_url
> ERROR: Cannot insert a duplicate key into unique index url_url
> ERROR: Cannot insert a duplicate key into unique index url_url
> ...
>
> anyone else using this combo and seeing this?
>
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
Re: [GENERAL] Dangling large objects
Dangling large objects? I thought this was a family list. :-) -- Brett PS. Sorry, couldn't resist... http://www.chapelperilous.net/~bmccoy/ --- Unnamed Law: If it happens, it must be possible.
[GENERAL] database names are all numbers now
after i ran vacuum analyze, i see that all my database directories with respect to their database names have changed to all numbers: # ls -l /var/pgsql/data total 17 -rw--- 1 pgsql wheel 4 Dec 30 15:45 PG_VERSION drwx-- 6 pgsql wheel 512 Jan 8 05:28 base drwx-- 2 pgsql wheel 512 Jan 3 14:21 global -rw--- 1 pgsql wheel 7399 Dec 30 15:45 pg_hba.conf -rw--- 1 pgsql wheel 1118 Dec 30 15:45 pg_ident.conf drwx-- 2 pgsql wheel 512 Jan 8 16:45 pg_xlog -rw--- 1 pgsql wheel 595 Jan 8 04:14 postgresql.conf -rw--- 1 pgsql wheel93 Jan 8 10:22 postmaster.opts -rw--- 1 pgsql wheel22 Jan 8 10:22 postmaster.pid # ls -l /var/pgsql/data/base total 8 drwx-- 2 pgsql wheel 1536 Dec 30 16:02 1 drwx-- 2 pgsql wheel 1536 Dec 30 15:45 18719 drwx-- 2 pgsql wheel 2048 Jan 8 16:31 397915 drwx-- 2 pgsql wheel 1536 Jan 8 05:28 9829118 is this normal?
[GENERAL] pgsql & mnogosearch
i'm starting to use mnogosearch 3.1.8 and pgsql-cvs on NetBSD/Alpha. i'm getting a tremendous amount of these errors in my log file when i'm running indexer: ... ERROR: Cannot insert a duplicate key into unique index url_url ERROR: Cannot insert a duplicate key into unique index url_url ERROR: Cannot insert a duplicate key into unique index url_url ERROR: Cannot insert a duplicate key into unique index url_url ERROR: Cannot insert a duplicate key into unique index url_url ... anyone else using this combo and seeing this?
Re: [GENERAL] Dangling large objects
Adam Haberlach <[EMAIL PROTECTED]> writes: > Is there any simple way for me to get a list of all large objects > in a database, so I can see if there are actually rows referring to them > and delete the ones that were not unlinked earlier? Look at the vacuum_lo contrib module. BTW, I believe lo_unlink doesn't need to be in a transaction block, only lo_open/read/write/close do. regards, tom lane
Re: [GENERAL] SELECT INTO Troubles
Brian Troxell <[EMAIL PROTECTED]> writes: > I have a PG/plSQL function get_attribute()) that does a simple lookup > using this code: > EXECUTE ''CREATE TEMPORARY TABLE random_tab (item) AS SELECT '' || >quote_ident(v_column) || '' FROM '' || >quote_ident(v_table_name) || '' WHERE '' || v_key_sql; > v_return := random_tab.item; > raise NOTICE ''v_return = %'', v_return; > DROP TABLE random_tab; You can't do it like that, because that breaks exactly the thing you are trying to get around, namely that non-EXECUTE queries in plpgsql are planned only once and the plan is cached. So both of the lines v_return := random_tab.item; DROP TABLE random_tab; (the first of which is implicitly a SELECT, remember) are going to fail on second and later iterations, because the cached plan refers to a table that ain't there anymore. While you can easily turn the DROP into EXECUTE ''DROP ...'', I'm not sure I see what to do about the other thing. You might have to give up and write the function in pltcl or plperl, which don't try to cache query plans (with the obvious implications for speed vs. flexibility). regards, tom lane
[GENERAL] Dangling large objects
I'm in a bit of a bind here. I wrote some code in our abstraction layer to automatically unlink large objects when the row referencing them is deleted (I know that they are only referenced once). However, I neglected to include them in a transaction, so I'm pretty sure that those unlinks never happened. Is there any simple way for me to get a list of all large objects in a database, so I can see if there are actually rows referring to them and delete the ones that were not unlinked earlier? -- Adam Haberlach|A cat spends her life conflicted between a [EMAIL PROTECTED] |deep, passionate, and profound desire for http://www.newsnipple.com |fish and an equally deep, passionate, and '88 EX500 |profound desire to avoid getting wet.
[GENERAL] hex input values
I cannot seem to insert hex values into an int column.
I must be doing something really stupid wrong, but I
can't see it.
insert into mytable (intcolumn) values (0xaabbcc);
parser error at "xaabbcc"
insert into mytable (intcolumn) values ('0xaabbcc');
pg_atoi...can't part "xaabbcc"
insert into mytable (intcolumn) values ('\0xaabbcc');
Inserts 0 (i.e. error/null value)
insert into mytable (intcolumn) values ('\\0xaabbcc');
pg_atoi...can't parse "\oaabbcc"
The pg_atoi() code calls strtol() which is documented
to handle hex values.
The error checking for EOS in pg_atoi() seems to be having
trouble with the leading zero.
Should I be storing these value as some other type?
thanks,
[EMAIL PROTECTED]
[GENERAL] SELECT INTO Troubles
Hello again everyone. This is the next message in my series of troubles regarding EXECUTE and SELECT INTO (or CREATE TABLE). Hopefully the list will be as helpful and informative as with my previous queries. I have a PG/plSQL function get_attribute()) that does a simple lookup using this code: EXECUTE ''CREATE TEMPORARY TABLE random_tab (item) AS SELECT '' || quote_ident(v_column) || '' FROM '' || quote_ident(v_table_name) || '' WHERE '' || v_key_sql; v_return := random_tab.item; raise NOTICE ''v_return = %'', v_return; DROP TABLE random_tab; In my test routine, I call this get_attribute() function, followed by another set_attribute() function, followed by get_attribute() again to make sure the value was changed. The problem is with the second call to get_attribute()...I get this error: psql:lop2:15: ERROR: Relation 74176 does not exist But if I remove the DROP TABLE statement from the get_attribute() function, the second call to it fails with this error: psql:lop2:15: ERROR: Relation 'random_tab' already exists So I tried it again, taking out the EXECUTE and just hard-coding the SELECT statement, and get the same errors. This sounds like a total Catch-22 to me, which must mean I'm doing something wrong. Help! Thanks in advance! Brian Troxell [EMAIL PROTECTED]
Re: [GENERAL] DROP SEQUENCE ?
Are you using the right name for the sequence> I am very new to sql so I might be off base here but the name of the sequence is: __id_seq sql snippet DROP TABLE members; DROP DROP SEQUENCE members_member_id_seq; DROP CREATE TABLE members ( member_id serial, member_type VARCHAR(10), usernameVARCHAR(20), passwordVARCHAR(20), prefix VARCHAR(10), name_first VARCHAR(20), name_last VARCHAR(20), name_suffix VARCHAR(10), address_street1VAR CHAR(25), address_street2VAR CHAR(25), address_cityVARCHAR(20), address_state VARCHAR(2), address_zip VARCHAR(10), phone VARCHAR(13), email VARCHAR(30), timedate_creation TIMESTAMP, timedate_modTIMESTAMP, rating INTEGER, preferences VARCHAR(100), account_bal NUMERIC(16,2) ); NOTICE: CREATE TABLE will create implicit sequence 'members_member_id_seq' for SERIAL column 'members.member_id' NOTICE: CREATE TABLE/UNIQUE will create implicit index 'members_member_id_key' for table 'members' CREATE imago - Original Message - From: "Ian deSouza" <[EMAIL PROTECTED]> To: <> Sent: Friday, January 05, 2001 9:57 PM Subject: [GENERAL] DROP SEQUENCE ? > Anybody know the syntax of the DROP SEQUENCE sql statement for PostgreSQL? > > Once I create a table with an attribute of type SERIAL, and drop the table, > I cannot recreate the table since the sequence already exists (and DROP > TABLE tableName does not remove the sequence entry). Does anybody know what > I would have to do to follow the DROP TABLE w/ to remove the "sequence" > created by the SERIAL datatype? > > Thanks in advance, Ian > > > >
Re: [GENERAL] Access 2000 and PostgreSQL: Record Editing Problems
On 5 Jan 01, at 14:37, Scott Teglasi wrote: > When I add a record, I fill in the relevant fields, but when I proceed > to the next row, the row I just added shows "#Deleted" in all of the > columns. It continues to do this as I add records. When I close the > table, then reopen it, my data is there, and the #Deleted is gone. It > does add the data, however when adding new records, and having > #Deleted shown on my newly added rows, is a bit of a nuisance.. > Anyone have any remedies? It's a FAQ: www.scw.org/pgaccess. BTW, questions about Pg + Access are better in pgsql-interfaces, which deals with interfaces to PostgreSQL. good luck, -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] ECPG could not connect to the database.
Peter Eisentraut <[EMAIL PROTECTED]> writes: >> Have you tried it lately? I suspect that you are depending on code that >> is not in libpq's current sources anymore. I fully agree with Peter E's >> reasons for removing it, too. We do not need to overload the definition >> of libpq's dbname parameter. > Ouch, it *is* documented in ecpg(1). I guess if ecpg wants to provide > this syntax (which it probably should, since the "sql connect to" syntax > doesn't have any other provisions for host name, port, etc.) then it could > take the code from libpq (it's still in there I think) and do the parsing > before calling PQsetdbLogin(). That would make sense to me. It would be a good idea to fix the bugs you were complaining of in November. The thing that jumped out at me in a quick look is that update_db_info is freeing the initial conn->dbName before it is done scanning it. regards, tom lane
RE: [GENERAL] backend corruption
> I think that under 7.1, pg_log is not so critical anymore, but I'm not > sure. Vadim, any comment? Still critical till we implement UNDO and true changes rollback on transaction abort. Vadim
RE: [GENERAL] Help with SQL Function
Thanks, Tom, and also to Alex Pilosov for his answer. I was extrapolating from the plpgsql docs, which I probably didn't understand correctly. Programming By Example (which is what we non-programmers are obliged to do) doesn't work so well when the docs are somewhat sparse. Are there any plans to expand the docs on plpgsql, since there are new features coming? Plpgsql looks like a good tool for enhancing my development of financial etc reports, but in working with it, I've had two weeks of frustration and not much of anything else so far. > -Original Message- > From: Tom Lane [SMTP:[EMAIL PROTECTED]] > Sent: Saturday, January 06, 2001 3:41 PM > To: Jeff Eckermann > Cc: [EMAIL PROTECTED] > Subject: Re: [GENERAL] Help with SQL Function > > Jeff Eckermann <[EMAIL PROTECTED]> writes: > > extracts=# create function testfunc(text) returns int4 as ' > > extracts'# select count (*) from dedcolo where equip_type = ''$1'' > --- That's doubled single quotes > > extracts'# ' language 'sql'; > > CREATE > > That's looking for rows where equip_type = '$1' ... ie, the literal > string $1. What you probably wanted is > > create function testfunc(text) returns int4 as ' > select count (*) from dedcolo where equip_type = $1 > ' language 'sql'; > > regards, tom lane
Re: [GENERAL] ECPG could not connect to the database.
Tom Lane writes: > > Not exactly. It is possible to use PGHOST but you also can add the hostname > > to the dbname. > > Have you tried it lately? I suspect that you are depending on code that > is not in libpq's current sources anymore. I fully agree with Peter E's > reasons for removing it, too. We do not need to overload the definition > of libpq's dbname parameter. Ouch, it *is* documented in ecpg(1). I guess if ecpg wants to provide this syntax (which it probably should, since the "sql connect to" syntax doesn't have any other provisions for host name, port, etc.) then it could take the code from libpq (it's still in there I think) and do the parsing before calling PQsetdbLogin(). -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
