Re: [GENERAL] PostgreSQL 8.0 Feature List?
Will PostgreSQL 8.0 include replication server (not contrib/*) and nested transactions support? -- WBR, sector119 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PostgreSQL 8.0 Feature List?
Am Dienstag, 10. August 2004 10:05 schrieb [EMAIL PROTECTED]: > Will PostgreSQL 8.0 include replication server (not contrib/*) and nested > transactions support? No and yes. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Using connection after fork
I'm looking at a database-using program (PostgreSQL/libpq and MySQL) which does the following: A daemon process opens a database connection, forks children on request, and those children access the database using that inherited connection. After one request, the child dies. It seems to me that this connection sharing cannot work because the processes would get mixed up, but the author claims that he has tested this successfully. I've been trying to figure out the semantics from the libc documentation, but I can't find a definitive answer. Does anyone know what will happen in this situation, and why it actually appears to work? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgreSQL 8.0 Feature List?
On Tue, 2004-08-10 at 02:05, [EMAIL PROTECTED] wrote: > Will PostgreSQL 8.0 include replication server (not contrib/*) and nested > transactions support? What difference does it make if replication is contrib/* or an external project or integrated? It's still the same thing. Plus, there are currently no replication systems for postgresql that are all things to all people, hence none are included or likely to be included. Slony-I just came out in beta, and it appears to be quite a nice replication system. To put it bluntly, which would you rather have, a database with integrated replication that had a flawed / unreliable replication system, or a database with an external replication system that works flawlessly? Integration is much less important than whether it functions, and anyone who says otherwise has been drinking too much marketing kool aid. nested transactions / savepoints will be included. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgreSQL 8.0.0 Officially Goes Beta
Thanks to all Developers, GREAT JOB! Just switched from 7.4 cygwin to 8 beta native win32 on my development system. Everything works so far! Just a small suggestion for now: Can you enable file logging (redirect_stderr = true) in the windows installer by default. I think without you'll see no logs when run as service. Best regards, Christian ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgreSQL 8.0 Feature List?
Scott Marlowe wrote: On Tue, 2004-08-10 at 02:05, [EMAIL PROTECTED] wrote: Will PostgreSQL 8.0 include replication server (not contrib/*) and nested transactions support? Slony-I just came out in beta, and it appears to be quite a nice replication system. I wonder if it would be a good idea from a propaganda perspective to include a reference to Slony-I in the press release and possibly the release notes? Or would such an imprimatur be inappropriate? Also, what is the etymology of the term Slony? Mike Mascari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL 8.0 Feature List?
As to Slony: in a few slavonic languages (I know about Czech, Slovak, Russian, maybe others too, "slony" is the plural of "slon", e.g. elephant. Thus Slony = Elephants. Cheers Zoltan Dňa Utorok 10. August 2004 11:51 ste napísali: > Scott Marlowe wrote: > > On Tue, 2004-08-10 at 02:05, [EMAIL PROTECTED] wrote: > >> Will PostgreSQL 8.0 include replication server (not contrib/*) > >> and nested transactions support? > > > > Slony-I just came out in beta, and it appears to be quite a nice > > replication system. > > I wonder if it would be a good idea from a propaganda perspective to > include a reference to Slony-I in the press release and possibly the > release notes? Or would such an imprimatur be inappropriate? > > Also, what is the etymology of the term Slony? > > Mike Mascari > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] error moving table to tablespace (8.0 beta win32 )
Hi! just playing with tablespaces... - moved a existing table to a new tablespace - tried to move it back to default tablespace (ALTER TABLE accounts SET TABLESPACE pg_default;) Got the following error in logfile: ERROR: could not create relation 1663/317186/317191: Permission denied Any suggestions? Best regards, Christian ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PostgreSQL 8.0 Feature List?
Am Dienstag, 10. August 2004 11:51 schrieb Mike Mascari: > I wonder if it would be a good idea from a propaganda perspective to > include a reference to Slony-I in the press release and possibly the > release notes? Or would such an imprimatur be inappropriate? It will probably be in the press release, but not in the release notes. > Also, what is the etymology of the term Slony? Russian: slon = elephant slony = elephants -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] row level lock
Title: row level lock Hello, I thought that with the new release I could lock at row level with nowait option, but it seems that nowait option is only for table level lock, so, Can I do a row level lock with nowait option? Thanks, pere
Re: [GENERAL] Listing views
Hi, What about mydb=# \dv ?? Doesn't it works fine. I don't see any pg views. Regards, Kaloyan Olivier Guilyardi wrote: Hi, I'm trying to list views, eliminating internal ones from the output. Using 7.2, I found this simple statement : SELECT viewname FROM pg_views WHERE viewname !~ '^pg_'; It works fine, ignoring 23 pg_* tables. And I get my actual views returned. But, with 7.4, I get many (about 30) more system views, as table_constraints, table_privileges, tables, etc... And these do not have any 'pg' prefix. Do you know of some query that would properly list views, wether it's running on Postgresql 7.4 or 7.2 and lower ? -- og ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] history tables with only one function?
Hi, I'm looking at the logging of a database we'll put in production soon. I've seen some posts on this list about history tables, like mentioned in http://www-106.ibm.com/developerworks/web/library/wa-dbdsgn2.html . I think I'll go that way too, but I still have some questions on the approach, and would appreciate any advice on it. Here are some questions I have: - is it possible to write only one function used for all logging triggers? As illustrated in http://www.varlena.com/varlena/GeneralBits/57.php , it is possible to use one function for tracking last update times for all tables: CREATE OR REPLACE FUNCTION setmodtime() RETURNS TRIGGER AS ' BEGIN NEW.modified_timestamp = now(); RETURN NEW; END ' LANGUAGE 'plpgsql'; Is it possible to create only one function to insert rows in the corresponding history table? The name of the history table can be constructed from the original table. and I guess all fields of the table can be retrieved from the db's metadata. Would that be feasible, and more importantly, would it be usable? -Another question I have is for those who use this approach: How often o you have to flush those history tables and when you flush the tables, where do you put the flushed data? In another database on another server or on tape? -Would it be possible to use the replication of Slony-I and only log in the history tables in the slave database? Or is somthing similar possible? Thanks. Raph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Listing views
On Tuesday 10 August 2004 03:14, Olivier Guilyardi wrote:
> SELECT viewname FROM pg_views WHERE viewname !~ '^pg_';
with 7.4 :
SELECT viewname FROM pg_views WHERE schemaname NOT IN
('pg_catalog','information_schema');
Albert
---(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] nested transaction
Title: nested transaction Hello, The new release includes savepoints, but I need to do a begin inside another begin and a commit/rollback after another commit/rollback. For example, I have a pA procedure that update some data, so this procedure execute a begin, after manipulate data and ends with commit/rollback. pA procedure can to be called directly by the client aplication. In other hand, I have a pB procedure that also update some data and calls pA procedure, so pB procedure execute a begin, after manipulate data, after call pA procedure and ends with commit/rollback. pB procedure can also to be called directly by the client aplication. The problem is that pA procedure can't do a new transaction, so what can I do? Is possible to detect from pA that there is a transaction and so do a savepoint instead of a begin? Thanks in advance, pere
Re: [GENERAL] Postgres update with self join
On Tue, 10 Aug 2004, Igor Kryltsov wrote: > Table looks like: > > select * from test; > name | code | master > --+---+ > ABC | 15074 | > ABC1 | 0 | ABC > ABC2 | 0 | ABC > EKL | 15075 | > EKL1 | 0 | EKL > (5 rows) > > > Now I need to replace "0" values in "code" column by corresponding "code" > values by following link between "master" field in a record where code=0 and > "name" field where it is not. > By the other words first two 0's have to be replaced with 15074 and last 0 > with 15075. > > This update works in MSSQL but in Postgres it replaces code values as shown > below. > > update test > set code = i1.code > from test i1 > join test i2 on i1.name = i2.master > where i2.code = 0; You probably need to be constraining the join between test and (i1 join i2). Maybe an additional where clause like "and test.name=i2.name" or something like that would work. Or, I think you can do this with a subselect which would have the advantage of not requiring extensions to the standard. Perhaps something like update test set code=(select code from test i2 where test.master=i2.name) where code=0; would do it. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Postgres update with self join
"Igor Kryltsov" <[EMAIL PROTECTED]> writes: > Now I need to replace "0" values in "code" column by corresponding "code" > values by following link between "master" field in a record where code=0 and > "name" field where it is not. > This update works in MSSQL but in Postgres it replaces code values as shown > below. > update test > set code = i1.code > from test i1 > join test i2 on i1.name = i2.master > where i2.code = 0; That query has no join condition to the target table, so it's hardly surprising that it updates everything in sight. If it "works" in MSSQL it must be because they are making some weird decision to pretend that one or the other of the mentions of test in the FROM clause ought to be identified with the target table. (If it acts as you want then they must be identifying "test i2" with "test", which is *really* weird --- you would think the first occurrence of test in the FROM would be the one they'd pick. I suppose this is another instance of an implementation bug becoming enshrined as a feature.) In Postgres you want to do something like this: update test set code = mst.code from test mst where test.master = mst.name and test.code = 0; To act exactly as you stated in words you'd probably also want to add "and mst.code <> 0". regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] hostory tables with a generic function?
Raphael Bauduin <[EMAIL PROTECTED]> writes: > - is it possible to write only one function used for all logging triggers? Yes, but it will be very hard and inefficient (maybe even impossible) in plpgsql. plpgsql isn't intended for dynamic field access and you'll really be fighting the language. I'd suggest writing in C, perhaps after proofing in pltcl. There are some sample C triggers in the contrib tree that do things fairly close to this; you might try adapting one of those. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] 7.4.3 server panic
I have been able to crash the server a few times with the following function
when it is called on a user and schema that was recently deleted. Following
is a log of what happened and also the function that was called. This is
7.4.3 on Freebsd 5.2.1. It doesn't always panic when it encounters a
deleted user/schema, it's kind of sporadic but happens often enough that
it's easy to duplicate.
Chris
--
CREATE OR REPLACE FUNCTION setuser(varchar) RETURNS integer AS
'
DECLARE
in_username ALIAS FOR \$1;
BEGIN
EXECUTE ''SET SESSION AUTHORIZATION '' || in_username;
EXECUTE ''SET SEARCH_PATH TO '' || in_username || '',PUBLIC'';
RETURN 1;
END '
LANGUAGE 'plpgsql';
-
LOG: statement: select * from
setuser('support')
LOG: statement: SET SESSION AUTHORIZATION support
CONTEXT: PL/pgSQL function "setuser" line 4 at execute statement
LOG: statement: SET SEARCH_PATH TO support,PUBLIC
CONTEXT: PL/pgSQL function "setuser" line 5 at execute statement
ERROR: schema "support" does not exist
CONTEXT: PL/pgSQL function "setuser" line 5 at execute statement
STATEMENT:select * from
setuser('support')
ERROR: invalid user ID: 194
PANIC: error during error recovery, giving up
LOG: server process (PID 38302) was terminated by signal 6
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Postgres update with self join
This is the way you do it in postgreSQL:
UPDATE
test
SET
code=T2.code
FROM
test T2
WHERE
test.code=0 AND
test.master=T2.name;
you need to specify the join condition in the WHERE clause.
On Tue, 2004-08-10 at 00:34, Igor Kryltsov wrote:
Hi,
If you can help me to correct my mistake.
To simplify my question:
I have table:
create table test (
name varchar(10),
code integer,
master varchar(10));
I have values:
insert into test values ('ABC', 15074, null);
insert into test values ('ABC1', 0, 'ABC');
insert into test values ('ABC2', 0, 'ABC');
insert into test values ('EKL', 15075, null);
insert into test values ('EKL1', 0, 'EKL');
Table looks like:
select * from test;
name | code | master
--+---+
ABC | 15074 |
ABC1 | 0 | ABC
ABC2 | 0 | ABC
EKL | 15075 |
EKL1 | 0 | EKL
(5 rows)
Now I need to replace "0" values in "code" column by corresponding "code"
values by following link between "master" field in a record where code=0 and
"name" field where it is not.
By the other words first two 0's have to be replaced with 15074 and last 0
with 15075.
This update works in MSSQL but in Postgres it replaces code values as shown
below.
update test
set code = i1.code
from test i1
join test i2 on i1.name = i2.master
where i2.code = 0;
select * from test;
name | code | master
--+---+
ABC | 15074 |
ABC1 | 15074 | ABC
ABC2 | 15074 | ABC
EKL | 15074 |
EKL1 | 15074 | EKL
(5 rows)
... all values 15074.
Thank you,
Igor
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
signature.asc
Description: This is a digitally signed message part
Re: [GENERAL] Problems with MS Visual Basic 6.0
Brigitte ROLLAND wrote:
> Hello !
> I need to intreact from VB6 with an PG 7.4.3 database, I've
> psqlodbc-7_03_0200 installed.
> I use the Microsoft ADO Data Control 6.0, and I've installed the SP5 for
> VB6.
Luc,
I have never used the ADO Data Control, so I can't be much help there -
I can tell you what I have done with a project I work on, which I have
converted to use PG via ODBC. Please note that I won't tell you
everything here, I have posted numerous times to both this list
[GENERAL] and the ODBC list on this issue - so search the archives.
> When I use the adodc control I see the data but I've problems with :
> - Update, on existing records : do nothing, I've to MoveFirst to store
> in database ! Why ?
For my project, I use the ActiveX Data Objects 2.7 Library for my
access. Even so, I have found that I need to do a
".movelast"/".movefirst" sequence in order to use the recordset I
return. I am not sure why this is, either - I have just accepted it and
moved on. Maybe the pointer in the recordset is on the last record, not
the first? Dunno...
> - Edit Method don't exist for that object, so EditMode properties is non
> signifiant,
Correct. Same for what I am doing. This is part of ADO (vs. DAO).
> - I can't update if I :
> use CheckBox control,
> use textBox control with a date.
Because you are using a control, and not the method I am using - I am
not sure how you can resolve this. I have found that for date type
fields that I can update them by using cdate():
ie, ![datefield] = cdate(text1.text)
Checkboxes I store in boolean fields, by taking the absolute value of
the checkbox value and storing that:
ie, ![booleanfield] = abs(checkbox.value)
Alternatively, you can also say:
![booleanfield] = (checkbox.value = 1)
> How can I resolve these points ?
> Regards.
> Luc
The basics of what I do to get a recordset for update/reading is as follows:
Dim conn As New ADODB.Connection
Dim recs As New ADODB.Recordset
'
' Open DSN-less ADO connection to database
'
Set conn = New ADODB.Connection
'
' In the following, replace the ??? in the connection string with values
' appropriate for your application.
'
conn.Open "driver={PostgreSQL};server=???;port=???;database=???;_
uid=???;pwd=???;ksqo=;"
'
' Retrieve recordset from table
'
Set recs = New ADODB.Recordset
'
recs.CursorLocation = adUseServer
'
recs.Open "SELECT * FROM ???", conn, adOpenDynamic, adLockOptimistic,_
adCmdText
'
' Manipulate recordset
'
With recs
'
If Not (.BOF And .EOF) Then
'
.MoveLast
.MoveFirst
'
' Update/Add/Read stuff here
'
End If
'
.Close
'
End With
'
Set recs = Nothing
'
' Close database connection
'
Set conn = Nothing
---
More information on all this, as I noted, is in the archives of this
list and the ODBC list (in fact, most of the it is probably in the ODBC
list, since I was posting there quite a lot when I did the conversion).
I know that the above is probably vastly different from using the ADO
control itself, but hopefully it might give you some ideas and insight
to allow you to get your application working...
Hope this helps,
Andrew Ayers
Phoenix, Arizona
-- CONFIDENTIALITY NOTICE --
This message is intended for the sole use of the individual and entity to whom it is
addressed, and may contain information that is privileged, confidential and exempt
from disclosure under applicable law. If you are not the intended addressee, nor
authorized to receive for the intended addressee, you are hereby notified that you may
not use, copy, disclose or distribute to anyone the message or any information
contained in the message. If you have received this message in error, please
immediately advise the sender by reply email, and delete the message. Thank you.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Replication options?
Thank you much, When I perform the google search: http://www.google.com/search?q=slony-i+review I do not find reviews and critiques of slony-i. Are there many companies using for their enterprise level database systems? Without reviews in the magazines, my bosses uncomfort with PostgreSQL will not be remedied with just project maintainers word of mouth. Liam From: Bruce Momjian <[EMAIL PROTECTED]> To: Liam Lesboch <[EMAIL PROTECTED]> CC: [EMAIL PROTECTED] Subject: Re: [GENERAL] Replication options? Date: Tue, 10 Aug 2004 13:12:39 -0400 (EDT) Most people are using Sloney for master/slave replication. You can search for it easily. --- Liam Lesboch wrote: > Greetings, > > Yesterday theres was a brief discuissions about replications software for > PostgreSQL. My boss and I saw only two replications for PostgreSQL that was > spoken of. We found no reviews on the internet that spoke of either or > compared them and we are considering PostgreSQL as an options for a movement > from our present platform of Microsoft SQL Server that has replications and > stored procedures. Can peoples direct me towards third-party reviews online > of the replications so that we can use as examples for consideration of > products and make migration? > > Thank you, > > Liam > > _ > Add photos to your e-mail with MSN 8. Get 2 months FREE*. > http://join.msn.com/?page=features/featuredemail > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] BIGINT indexes still with problems
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Dan Ruthers wrote: | Now, if I run this query (note the int8 cast - also tried with the '' cast to String, same results): | test=> explain select * from dmaildatum where idparent=int8(783219); | QUERY PLAN | -- | Seq Scan on dmaildatum (cost=0.00..2241.71 rows=2229 width=272) |Filter: (idparent = 783219::bigint) | (2 rows) | | The index is not used. But with an identical query, only different parameter value: | desknow=> explain select * from dmaildatum where idparent=int8(1187838); | QUERY PLAN | | | --- | Index Scan using ix_dmaildatum_idparent on dmaildatum (cost=0.00..284.05 rows= | 102 width=272) |Index Cond: (idparent = 1187838::bigint) | (2 rows) | | The index is used! | I also did a vacuum analyze, and restarted Postgres and it did not make any difference. | I tried many other ID values (ex 783218 and 783220), and they seem to use the index correctly. Only that value doesn't. | | Can anyone explain why Postgres behaves differently in these two cases, or at least point to some hints? Because this means that a sequential scan is better for that value. Perform this selects: (1) select count(*) from dmaildatum; (2) select count(*) from dmaildatum where idparent=int8(783219); (3) select count(*) from dmaildatum where idparent=int8(1187838); I bet that the ratio (2)/(1) is greater then (3)/(1). Now show us the following results: explain analyze select * from dmaildatum where idparent=int8(783219); explain analyze select * from dmaildatum where idparent=int8(1187838); and repeat it again but executing before: set enable_seqscan = off; Depending on the results that you get may be you need to lower the index scan cost tuning the cpu related GUC variables. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBGSfL7UpzwH2SGd4RAgBsAKCXvs2L/XUEmSGxBzEiAHmWasgShACeLvjp 9m12DSnj2tBuGSgldr4D9Po= =KTil -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] eWeek Reviews Bricolage
eWeek has reviewed Bricolage, the Perl-powered, PostgreSQL-backed open-source content management system. The article was published yesterday. An excerpt: Bricolage is quite possibly the most capable enterprise-class open-source application available. The Web content management application features excellent administration capabilities, and it is highly extensible and capable of managing even the biggest and most complex Web sites. As an open-source product, Bricolage is free, and companies can now purchase support and development services from Kineticode. http://www.eweek.com/article2/0,1759,1627959,00.asp The article is part of the "Content Management Face-Off" in the current issue of eWeek: Included in this evaluation are the open-source Bricolage 1.8.1, Interwoven Inc.'s TeamSite 6.1, CrownPeak Technology Inc.'s Advantage CMS, Serena Software Inc.'s Collage 4.5, PaperThin Inc.'s CommonSpot Content Server 4.0 and Ektron Inc.'s CMS300 4.5. (The reviews are ordered, roughly, from the high end to the low end of the content management market.) http://www.eweek.com/article2/0,1759,1627957,00.asp Regards, David smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] PostgreSQL 8.0.0 Officially Goes Beta
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Marc G. Fournier wrote: | On Tue, 10 Aug 2004, Rajesh Kumar Mallah wrote: | |> |> The beta link under ftp://ftp3.us.postgresql.org/pub/postgresql/ |> and possible all mirrors leads to nowhere. I guess many people |> would click there. | | | Already fixed ... s | Doesn't work, I just check the url not the link, testing the link, this is what a Squid say: The following URL could not be retrieved: ftp://ftp3.us.postgresql.org/pub/postgresql/beta Squid sent the following FTP command: RETR beta and then received this reply beta: No such file or directory. This might be caused by an FTP URL with an absolute path (which does not comply with RFC 1738). If this is the cause, then the file can be found at ftp://ftp3.us.postgresql.org/%2f/pub/postgresql/beta. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBGSV67UpzwH2SGd4RAkdsAKCnmCbZEiXPzA/TnKWcXGqmyNvB/gCdHjW+ KT+kU9eT4z9SDw0IHhRHpqE= =Io4A -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL 8.0 Feature List?
At 8/10/04 9:30 AM, [EMAIL PROTECTED] wrote: >Slony-I is released... [snip] > >> >> Also, what is the etymology of the term Slony? > >Elephants, especially this one: http://slony.info Is the project called "Slony-1" or "Slony1" (numeral one), or "Slony-I" (uppercase i)? It appears to be referred to multiple ways, even on the Web site, which means that it's difficult to search for and I don't know how to pronounce it when I talk to other people. (I'm convinced that hard-to-pronounce names make people decide not to mention cool things to other people for fear they'll look stupid, directly resulting in less word-of-mouth.) -- Robert L Mathews, Tiger Technologies http://www.tigertech.net/ "Ignorance more frequently begets confidence than does knowledge." -- Darwin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] BIGINT indexes still with problems
On Mon, 9 Aug 2004, Dan Ruthers wrote: > Now, if I run this query (note the int8 cast - also tried with the '' cast to > String, same results): > test=> explain select * from dmaildatum where idparent=int8(783219); > QUERY PLAN > -- > Seq Scan on dmaildatum (cost=0.00..2241.71 rows=2229 width=272) >Filter: (idparent = 783219::bigint) > (2 rows) > > The index is not used. But with an identical query, only different parameter value: > desknow=> explain select * from dmaildatum where idparent=int8(1187838); > QUERY PLAN > > > --- > Index Scan using ix_dmaildatum_idparent on dmaildatum (cost=0.00..284.05 rows= > 102 width=272) >Index Cond: (idparent = 1187838::bigint) > (2 rows) Look at the row estimates for the two cases. How many rows are actually returned and how long the queries take (explain analyze will give that information)? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] pg_restore with Fc fails with [archiver] out of memory error
Created a pg_dump with Fc (custom format compression) that resulted in a 300+MB file. Now trying to pg_restore this thing fails with either an out of memory error (as in the subject line) on FreeBSD 4.10-STABLE or a: pg_restore in malloc(): error: allocation failed Abort (core dumped) error on FreeBSD 5.2.1-P9 (RELEASE) In both cases I have increased the max data segment size to 1.5GB and the max stack size to 768M or so. So obviously we have some error message when malloc'ing for the AR structure. Any ideas here? Is it trying to stuff the 300MB file into memory and uncompress and sort it there? Has anyone found a workaround for this? (I have found similar questions and zero answers doing google searches and mailing list searches). Is there any way of uncompressing this file *other* than using pg_restore? Sven p.s. Although I will check the archives, I would appreciate a CC on any responses. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] BIGINT indexes still with problems
Dan Ruthers wrote: > The index is used! > I also did a vacuum analyze, and restarted Postgres and it did not > make any difference. I tried many other ID values (ex 783218 and > 783220), and they seem to use the index correctly. Only that value > doesn't. Possibly, that is the most common value and the cost calculation yields that it would be more efficient to not use the index. If you disagree, please show the timings generated by EXPLAIN ANALYZE. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL 8.0.0 Officially Goes Beta
Marc G. Fournier wrote: For a complete list of changes/improvements since 7.4.0 was released, please see: http://developer.postgresql.org/beta-history.txt I think is better write in the Win32 Native Server section that Postgres is only available on Win32 with NTFS file system. Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Relation does not exist
In general, it seems to correspond to when there are a lot of inserts being run. I have one application that maybe does 10 inserts within 15 seconds or so, at which point the problem seems to crop up. Any operation that does not use the qualified schema.entity names will error out (selects, inserts, updates) Taking your advice on upgrading both OS and pg version. I'll be back if that does not solve the issue ;-) Thanks for your time. -Rob -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 10, 2004 2:30 PM To: Rob Klaus Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] Relation does not exist "Rob Klaus" <[EMAIL PROTECTED]> writes: > We are having a problem where intermittently a query will not work, > returning an error: > ERROR: Relation "x" does not exist > It will do this for a variable period, typically not lasting more than 5 > minutes This certainly seems like a bug, but I have no idea what's causing it. Can you characterize the conditions under which the bug appears or disappears? What operation exactly is getting the error, and what sorts of things are happening in parallel with it? > We are using PG_VERSION 7.3 on RedHat 7.0 Hm. Red Hat 7.0 is positively ancient; you really ought to update. And if you are running original PG 7.3 there are some very serious bugs that you ought to fix by updating to 7.3.6 (if not 7.4.something). But I can't say that I recognize your report as related to any of the known bug fixes. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Relation does not exist
"Rob Klaus" <[EMAIL PROTECTED]> writes: > We are having a problem where intermittently a query will not work, > returning an error: > ERROR: Relation "x" does not exist > It will do this for a variable period, typically not lasting more than 5 > minutes This certainly seems like a bug, but I have no idea what's causing it. Can you characterize the conditions under which the bug appears or disappears? What operation exactly is getting the error, and what sorts of things are happening in parallel with it? > We are using PG_VERSION 7.3 on RedHat 7.0 Hm. Red Hat 7.0 is positively ancient; you really ought to update. And if you are running original PG 7.3 there are some very serious bugs that you ought to fix by updating to 7.3.6 (if not 7.4.something). But I can't say that I recognize your report as related to any of the known bug fixes. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] psql: immediately exit after an error?
Can psql be told to exit immediately after an error (especially when
doing commands from a file, -f)? This is the default behaviour of the
mysql client, except when we give it -f option ("force").
The problem is, when restoring a dump, a failure at the some point might
cause the subsequent commands to produce wrong results (e.g. I redefine
a builtin function with a plruby function with different behaviour, but
plruby failed to be installed due to wrong path. Thus the subsequent
commands are executed using the builtin function which is not the
expected one.) Furthermore, you can't check on psql exit code to see
whether _any_ command was not successfully executed.
Of course one should examine the full psql output after a restore
anyway, and the option to exit immediately after an error can save time
(especially for large dumps).
--
dave
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] PostgreSQL 8.0.0 Officially Goes Beta
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Rajesh Kumar Mallah wrote: | | The beta link under ftp://ftp3.us.postgresql.org/pub/postgresql/ | and possible all mirrors leads to nowhere. I guess many people | would click there. It works for me. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBGQT47UpzwH2SGd4RAtdAAJ0Y3H6MyIPbqMJxJ7DIJBADXwgHLgCg1Rta heomRx//60ZjhDOdG/18D3A= =SSHT -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] plruby issue with redeclaring functions
David Garamond wrote: -- plruby 0.4.2, ruby 1.8.1, pg 7.4.3, linux Just to note here that the plruby has fixed this issue. He apparently does not read -general daily, so I'll post to ruby-talk mailing list in the future. -- dave ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Replication options?
Most people are using Sloney for master/slave replication. You can search for it easily. --- Liam Lesboch wrote: > Greetings, > > Yesterday theres was a brief discuissions about replications software for > PostgreSQL. My boss and I saw only two replications for PostgreSQL that was > spoken of. We found no reviews on the internet that spoke of either or > compared them and we are considering PostgreSQL as an options for a movement > from our present platform of Microsoft SQL Server that has replications and > stored procedures. Can peoples direct me towards third-party reviews online > of the replications so that we can use as examples for consideration of > products and make migration? > > Thank you, > > Liam > > _ > Add photos to your e-mail with MSN 8. Get 2 months FREE*. > http://join.msn.com/?page=features/featuredemail > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL 8.0.0 Officially Goes Beta
The beta link under ftp://ftp3.us.postgresql.org/pub/postgresql/ and possible all mirrors leads to nowhere. I guess many people would click there. Regds mallah. Gaetano Mendola wrote: Marc G. Fournier wrote: After almost 9 months of development, the PostgreSQL Global Development Group is proud to announce that development on PostgreSQL 8.0.0 has now finished, and is ready for some serious testing. I submitted the news to www.osnews.com, do you think that is too much to submit it to slashdot ? Just to avoid to be slashdotted... Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- regds Mallah. Rajesh Kumar Mallah +---+ | Tradeindia.com (3,11,246) Registered Users | | Indias' Leading B2B eMarketPlace | | http://www.tradeindia.com/ | +---+ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Using connection after fork
Jeff <[EMAIL PROTECTED]> writes: > I've been bit by this sort of thing before. When you fork() the child > inherits all the file descriptors , including the one connected to PG. > But when the child dies that FD will be closed, thus it will also be > closed in the parent causing "odd behavior". (At least, that is what > I've seen in my apps) That would only happen if the child explicitly does PQfinish() before exiting, so that the backend sees an 'X' (Terminate) command arrive and therefore cuts the connection from its end. If the child just exits the pipe or TCP connection will remain open. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] dump/restore from 7.4.3linux to 8beta1 win32
I did the pg_dump with the Linux 7.4.3 version and tried to pg_restore with Win32 8beta1 version and I got: C:\TEMP>pg_restore -d desarrollo -i -U postgres xdump pg_restore: [archiver] input file does not appear to be a valid archive Taken from the help "For best results, however, try to use the pg_dumpall command from PostgreSQL 8.0.0beta1, since this version contains bug fixes and improvements over older versions. While this advice might seem idiosyncratic since you haven't installed the new version yet, it is advisable to follow it if you plan to install the new version in parallel with the old version." Is there a way to get the pg_dump 8beta1 version for Linux without installing the whole package? any idea or suggestion? Thanks in advance, -- Sinceramente, Josué Maldonado. "¿Por qué cuanto más retrasados estamos, más dura la luz roja del semáforo?" ---(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] Sorting varchar w/single digits
I have varchar column with both numbers and letters, like 1 thru 10 and 5A thru 5G, they are unit numbers for apartments. If I have 1 thru 100, since it is a varchar field, it sorts like 1,10,11... instead of 1,2,3... Is there any way to handle this without having to make a sort order column? -- Robert ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Create Table with Foreign Key Error
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 John Haney wrote: | Postgresql 7.4.3-1 under Cygwin. | | I created a table called ServerTypes: | | CREATE TABLE ServerTypes( | ServerTypeID SERIAL UNIQUE NOT NULL, | Type TEXT PRIMARY KEY); | | Works fine. | | Now, I want to create a table called servers with a Foreign Key | referencing ServerTypes.Type: | | CREATE TABLE Servers( | ServerID SERIAL UNIQUE NOT NULL, | Type REFERENCES ServerTypes (Type), | Server TEXT PRIMARY KEY); You forgot to specify the data type for the field Type. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBGVHq7UpzwH2SGd4RAriNAKDRKJCpgGen8VVsxg//rmjqU+O6vgCg4u/9 9zcAUYNCfaeU2i9WVTXdh3k= =5YeH -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] pg_restore with Fc fails with [archiver] out of memory
Tom Lane wrote: Sven Willenberger <[EMAIL PROTECTED]> writes: pg_restore in malloc(): error: allocation failed Abort (core dumped) A stack trace from that core file would be useful in figuring out what the problem is... also, what PG version is this? regards, tom lane psql -V psql (PostgreSQL) 7.4.3 contains support for command-line editing gdb: Reading symbols from /usr/local/lib/libpq.so.3...done. Loaded symbols for /usr/local/lib/libpq.so.3 Reading symbols from /usr/local/lib/libintl.so.6...done. Loaded symbols for /usr/local/lib/libintl.so.6 Reading symbols from /usr/local/lib/libssl.so.3...done. Loaded symbols for /usr/local/lib/libssl.so.3 Reading symbols from /usr/local/lib/libcrypto.so.3...done. Loaded symbols for /usr/local/lib/libcrypto.so.3 Reading symbols from /lib/libz.so.2...done. Loaded symbols for /lib/libz.so.2 Reading symbols from /lib/libreadline.so.4...done. Loaded symbols for /lib/libreadline.so.4 Reading symbols from /lib/libcrypt.so.2...done. Loaded symbols for /lib/libcrypt.so.2 Reading symbols from /lib/libm.so.2...done. Loaded symbols for /lib/libm.so.2 Reading symbols from /lib/libutil.so.4...done. Loaded symbols for /lib/libutil.so.4 Reading symbols from /lib/libc.so.5...done. Loaded symbols for /lib/libc.so.5 Reading symbols from /usr/local/lib/libiconv.so.3...done. Loaded symbols for /usr/local/lib/libiconv.so.3 Reading symbols from /lib/libncurses.so.5...done. Loaded symbols for /lib/libncurses.so.5 Reading symbols from /libexec/ld-elf.so.1...done. Loaded symbols for /libexec/ld-elf.so.1 #0 0x282a3dcf in kill () from /lib/libc.so.5 (gdb) bt #0 0x282a3dcf in kill () from /lib/libc.so.5 #1 0x28298878 in raise () from /lib/libc.so.5 #2 0x28310f82 in abort () from /lib/libc.so.5 #3 0x2830f6fe in tcflow () from /lib/libc.so.5 #4 0x2830ff9b in tcflow () from /lib/libc.so.5 #5 0x28310356 in malloc () from /lib/libc.so.5 #6 0x0804c9a1 in ReadStr (AH=0x805e000) at pg_backup_archiver.c:1530 #7 0x0804d2d8 in ReadToc (AH=0x805e000) at pg_backup_archiver.c:1878 #8 0x0804fa94 in InitArchiveFmt_Custom (AH=0x805e000) at pg_backup_custom.c:196 #9 0x0804cf7b in _allocAH (FileSpec=0xbfbfed19 "./callingdata", fmt=archCustom, compression=0, mode=archModeWrite) at pg_backup_archiver.c:1743 #10 0x0804a628 in OpenArchive (FileSpec=0x0, fmt=archUnknown) at pg_backup_archiver.c:103 #11 0x0804a22a in main (argc=5, argv=0xbfbfebe8) at pg_restore.c:333 #12 0x08049b52 in _start () Frame 6 - 11: (gdb) up 6 #6 0x0804c9a1 in ReadStr (AH=0x805e000) at pg_backup_archiver.c:1530 1530pg_backup_archiver.c: No such file or directory. in pg_backup_archiver.c (gdb) up #7 0x0804d2d8 in ReadToc (AH=0x805e000) at pg_backup_archiver.c:1878 1878in pg_backup_archiver.c (gdb) up #8 0x0804fa94 in InitArchiveFmt_Custom (AH=0x805e000) at pg_backup_custom.c:196 196 pg_backup_custom.c: No such file or directory. in pg_backup_custom.c (gdb) up #9 0x0804cf7b in _allocAH (FileSpec=0xbfbfed19 "./callingdata", fmt=archCustom, compression=0, mode=archModeWrite) at pg_backup_archiver.c:1743 1743pg_backup_archiver.c: No such file or directory. in pg_backup_archiver.c (gdb) up #10 0x0804a628 in OpenArchive (FileSpec=0x0, fmt=archUnknown) at pg_backup_archiver.c:103 103 in pg_backup_archiver.c (gdb) up #11 0x0804a22a in main (argc=5, argv=0xbfbfebe8) at pg_restore.c:333 333 pg_restore.c: No such file or directory. in pg_restore.c Let me know if you want any more details, a disassembled frame, what have you. Thanks, Sven ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] pg_restore with Fc fails with [archiver] out of memory error
Sven Willenberger <[EMAIL PROTECTED]> writes: > pg_restore in malloc(): error: allocation failed > Abort (core dumped) A stack trace from that core file would be useful in figuring out what the problem is... also, what PG version is this? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] 7.4.3 server panic
"Chris Ochs" <[EMAIL PROTECTED]> writes: > ERROR: invalid user ID: 194 > PANIC: error during error recovery, giving up > LOG: server process (PID 38302) was terminated by signal 6 Can you get a stack traceback from this crash? The only occurrence of "invalid user ID:" that I see in the source code is in GetUserNameFromId(), but there's no visible reason why that would be called during error recovery. Also, a recipe for reproducing it would help. I spent a little time trying with your function with no success. You might be able to make it more reproducible by inserting delays in the setuser() function. (See the sleep() function in src/test/regress/sql/stats.sql for a quick and dirty way to delay.) I didn't have any luck, but since I don't know what's going on concurrently with this function in your environment, I was probably trying the wrong things. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Locks in functions?
sorry, i wrote the funciton wrong. here is what i meant to write: CREATE OR REPLACE FUNCTION public.sample(varchar) RETURNS SETOF record AS ' DECLARE row RECORD; BEGIN LOCK TABLE table1 IN ROW SHARE MODE; FOR urlrow in EXECUTE \'SELECT * FROM "table1"\' LOOP UPDATE "table1" SET col1=5 WHERE "col2"=6 RETURN NEXT row; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql' VOLATILE; thanx, amir On Tue, 10 Aug 2004 15:53:33 -0700, Amir Zicherman <[EMAIL PROTECTED]> wrote: > will locks work inside functions or only in transactions? > > will this lock? > > CREATE OR REPLACE FUNCTION public.sample(varchar) > RETURNS SETOF record AS > ' > DECLARE > row RECORD; > BEGIN > LOCK TABLE "URL" IN ROW SHARE MODE; > FOR urlrow in EXECUTE \'SELECT * FROM "table1"\' > LOOP >UPDATE "table1" SET col1=5 WHERE "col2"=6 >RETURN NEXT row; > END LOOP; > RETURN; > END; > ' > LANGUAGE 'plpgsql' VOLATILE; > > thanx, amir > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Transaction blocks
On Tue, 2004-08-10 at 14:45, Jonathan Barnhart wrote: > Is there a way to continue a transaction after an error? I've got a > situation where I have a transaction open and I'm inserting data, but > some of it could fail on validation. I want to keep the good stuff, > leave out the bad, and get the speed of not having to commit every > record seperately. For a specific example, I'm importing a list of > stocks, but some symbols have changed because the list is old, so there > are invalid symbols in the list that are not found in the master list. > I want to catch the invalid ones and fix them, but still commit the > good ones. Right now I'm committing each record, but it's slow. That feature (save points / nested transactions) will be in 8.0, which just entered beta, however, it will still have the issue that you'll be making a bunch of transactions, so to speak, so the speed likely won't be better, just the error handling for what you're trying to do. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Locks in functions?
will locks work inside functions or only in transactions? will this lock? CREATE OR REPLACE FUNCTION public.sample(varchar) RETURNS SETOF record AS ' DECLARE row RECORD; BEGIN LOCK TABLE "URL" IN ROW SHARE MODE; FOR urlrow in EXECUTE \'SELECT * FROM "table1"\' LOOP UPDATE "table1" SET col1=5 WHERE "col2"=6 RETURN NEXT row; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql' VOLATILE; thanx, amir ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Replication options?
Thes slashdots post today about the beta releases of 8.0 caught the attention of my boss and I. Many comments about the replicator issue and saw many posts about Slony-I in particular. Maybe this is the only viable option in PostgreSQL? There are others that cost money but no where did we surface a article that spoke of them in any form of critique or tutorial (good or bad) and thats a concern for my boss. What large companies use replicators for PostgreSQL? Liam From: "Liam Lesboch" <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: [GENERAL] Replication options? Date: Tue, 10 Aug 2004 20:29:08 + Thank you much, When I perform the google search: http://www.google.com/search?q=slony-i+review I do not find reviews and critiques of slony-i. Are there many companies using for their enterprise level database systems? Without reviews in the magazines, my bosses uncomfort with PostgreSQL will not be remedied with just project maintainers word of mouth. Liam From: Bruce Momjian <[EMAIL PROTECTED]> To: Liam Lesboch <[EMAIL PROTECTED]> CC: [EMAIL PROTECTED] Subject: Re: [GENERAL] Replication options? Date: Tue, 10 Aug 2004 13:12:39 -0400 (EDT) Most people are using Sloney for master/slave replication. You can search for it easily. --- Liam Lesboch wrote: > Greetings, > > Yesterday theres was a brief discuissions about replications software for > PostgreSQL. My boss and I saw only two replications for PostgreSQL that was > spoken of. We found no reviews on the internet that spoke of either or > compared them and we are considering PostgreSQL as an options for a movement > from our present platform of Microsoft SQL Server that has replications and > stored procedures. Can peoples direct me towards third-party reviews online > of the replications so that we can use as examples for consideration of > products and make migration? > > Thank you, > > Liam > > _ > Add photos to your e-mail with MSN 8. Get 2 months FREE*. > http://join.msn.com/?page=features/featuredemail > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Replication options?
On Tue, 2004-08-10 at 16:53, Liam Lesboch wrote: > Thes slashdots post today about the beta releases of 8.0 caught the > attention of my boss and I. Many comments about the replicator issue and saw > many posts about Slony-I in particular. Maybe this is the only viable option > in PostgreSQL? There are others that cost money but no where did we surface > a article that spoke of them in any form of critique or tutorial (good or > bad) and thats a concern for my boss. What large companies use replicators > for PostgreSQL? Back in the days of 6.5.3 when we were looking at using it at the last company I was at, I built my own test suite to make sure postgresql could handle the load we were going to throw at it. No matter what the guys in the nice suits from big companies tell you to sell their product, you owe it to yourself and your company to prove / disprove the THEORY that a particular piece of software will do what you need. Who knows, maybe you could be the one writing the article on Slony-I that someone else reads before they try it. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Sorting varchar w/single digits
If your string *always* begins with a numeral, this will work:
ORDER BY to_number(text_field, text()), text_field
If it doesn't always begin with a numeral, you have to ensure that it does, so a
textcat of zero
ensure it does...
ORDER BY to_number(textcat('0', text_field), text()), text_field
That works provided your number is never negative, (a reasonable assumption I think).
Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
[EMAIL PROTECTED]
Fax: (416) 441-9085
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of Robert
> Fitzpatrick
> Sent: Tuesday, August 10, 2004 8:55 PM
> To: PostgreSQL
> Subject: [GENERAL] Sorting varchar w/single digits
>
>
> I have varchar column with both numbers and letters, like 1
> thru 10 and
> 5A thru 5G, they are unit numbers for apartments. If I have 1
> thru 100,
> since it is a varchar field, it sorts like 1,10,11... instead of
> 1,2,3...
>
> Is there any way to handle this without having to make a sort order
> column?
>
> --
> Robert
>
>
> ---(end of
> broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> [EMAIL PROTECTED])
>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Sorting varchar w/single digits
On Tue, Aug 10, 2004 at 08:54:45PM -0400, Robert Fitzpatrick wrote: > I have varchar column with both numbers and letters, like 1 thru 10 and > 5A thru 5G, they are unit numbers for apartments. If I have 1 thru 100, > since it is a varchar field, it sorts like 1,10,11... instead of > 1,2,3... > > Is there any way to handle this without having to make a sort order > column? Try something like this: ORDER BY SUBSTRING(unitnum FROM '[0-9]+')::INTEGER, unitnum -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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] Losing records when server hang
Chris Travers wrote: lec wrote: Scott Marlowe wrote: On Sun, 2004-08-08 at 19:43, lec wrote: Hi, I'm observing the following: If I commit the following records 1,2,3,4,5,6,7,8,9,10 to the database and the server hangs, I could lose records 5,6,7,8,9 but record 10 is there. How is this possible and do anyone know how Postgresql physically writes the records? Assuming a properly function storage subsystem and a kernel that does not lie about fsync, this is not possible. Are you running on top of IDE drives with the write cache enabled? Most, if not all, IDE drives lie about FSYNC and can lose data if power is disconnected abrubtly. SCSI Or if you are running with fsync=false in postgresql.conf the same thing can happen. Otherwise, not, it should not happen. More info perhaps? I didn't change the default fsync, which I believe is fsync=true. Could your SCSI controller be misbehaving? Could this be leading to a system hang (say when swap space is being called back into memory)? The first hardware that got replaced was the SCSI controller. After that there were still hardware "hanging" and eventually the system engineers replaced the whole server and they weren't sure what the fault was. The lost transactions happened not on the first server hang. I'm more curious to know why the transactions in the middle got lost but the last transaction was there. Thanks, lec ---(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] 7.4.3 server panic
"Chris Ochs" <[EMAIL PROTECTED]> writes: ERROR: invalid user ID: 194 PANIC: error during error recovery, giving up LOG: server process (PID 38302) was terminated by signal 6 Can you get a stack traceback from this crash? The only occurrence of "invalid user ID:" that I see in the source code is in GetUserNameFromId(), but there's no visible reason why that would be called during error recovery. (gdb) bt #0 0x284f9dcf in kill () from /lib/libc.so.5 #1 0x284ee878 in raise () from /lib/libc.so.5 #2 0x28566f82 in abort () from /lib/libc.so.5 #3 0x08226a6a in errfinish () #4 0x08226953 in errfinish () #5 0x0822f54d in GetUserNameFromId () #6 0x081183a3 in show_session_authorization () #7 0x08232fe7 in show_all_settings () #8 0x0823196b in AtEOXact_GUC () #9 0x0823164f in AtEOXact_GUC () #10 0x08094cbd in XactPopRollback () #11 0x081a2334 in PostgresMain () #12 0x0817576b in PostmasterMain () #13 0x08174fa3 in PostmasterMain () #14 0x08173436 in PostmasterMain () #15 0x08172c1c in PostmasterMain () #16 0x0813b7fa in main () #17 0x0806d822 in _start () (gdb) Also, a recipe for reproducing it would help. I spent a little time trying with your function with no success. You might be able to make it more reproducible by inserting delays in the setuser() function. (See the sleep() function in src/test/regress/sql/stats.sql for a quick and dirty way to delay.) I didn't have any luck, but since I don't know what's going on concurrently with this function in your environment, I was probably trying the wrong things. I'm not sure myself exactly what the trigger is. The database connections are all through mod perl/Apache::DBI which I suspect have something to do with the problem. Maybe when a user is dropped, it changes the state of the database in some way that an active, open connection doesn't pick up? Just a guess. One thing I noticed is that the panic happens not when setuser is called on the user that is deleted, but on a user that doesn't, and never did, exist. It also keeps panicing on the same user even though I am calling setuser on several other users that don't exist (or did and have been deleted). It does appear though that deleting a user/schema has some sort of effect, because it always seems to happen when I have deleted a user/schema. In other words it's never happened in the absence of a recently deleted user that setuser was called on, even though it's not that user that it appears to panic on. If that makes sense Chris regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] postmaster does not shut down
I am getting 'postmaster does not shut down' when trying to stop the database with '.../pg_ctl -D /path/to/datadir stop -m fast. How should I proceed to get the database shut down and restarted? Are there other options besides immediate shutdown flag? I dont' want to lose everything I've worked on today :( -- Robert ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] shared_buffers Question
BTW Joe, I sent my earlier suggestion to you directly. You might want to talk to your email admin to find out why your server bounced a perfectly innocuous message thusly: <[EMAIL PROTECTED]>: 12.47.0.10 failed after I sent the message. Remote host said: 550 Message Returned: For some reason, your e-mail was unable to be delivered to Sweetwater. This may be due to a content filter on our server. If your message is legitimate and contains questionable content, please remove it and try re-sending. If you feel this is an error, please contact your Sweetwater Representative or our E-mail Administrator at 1-800-222-4700 x1198 to resolve this issue. We apologize for any inconvenience this may have caused. #D702 -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665-7007 voice ---(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] shared_buffers Question
> Your shared buffers are almost certainly not the problem here. 2000 > shared buffers is only 16 Megs of ram, max. More than likely, the > database filled up the data directory / partition because it wasn't > being vacuumed. Yes. Also check to make sure that some rogue process somewhere isn't filling your hard disk with some huge log file. I don't remember the UNIX commands offhand, but you should sudo a search starting in / for all large files, say > 1GB for instance. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665-7007 voice ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] error moving table to tablespace (8.0 beta win32 )
Christian Traber wrote: Hi! just playing with tablespaces... - moved a existing table to a new tablespace - tried to move it back to default tablespace (ALTER TABLE accounts SET TABLESPACE pg_default;) Got the following error in logfile: ERROR: could not create relation 1663/317186/317191: Permission denied Any suggestions? are you able to create new tables in the default tablespace ? looks like a permission issue did you initdb a fresh folder see if the owner of $PGDATA/data/base is the user that running postmaster (usually postgres) . It works fine for me though Regds mallah test=# \d t_b Table "public.t_b" Column | Type | Modifiers +-+--- a | integer | Tablespace: "space" test=# ALTER TABLE t_b SET TABLESPACE pg_default; ALTER TABLE test=# SELECT version(); version -- PostgreSQL 8.0.0beta1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 (Mandrake Linux 10.0 3.3.2-6mdk) (1 row) test=# Best regards, Christian ---(end of broadcast)--- TIP 8: explain analyze is your friend -- regds Mallah. Rajesh Kumar Mallah +---+ | Tradeindia.com (3,11,246) Registered Users | | Indias' Leading B2B eMarketPlace | | http://www.tradeindia.com/ | +---+
Re: [GENERAL] psql: immediately exit after an error?
I would think that depends upon how the sql in the file is coded. You can use the
RAISE NOTICE / ERROR commands to abort a function's execution.
> Can psql be told to exit immediately after an error (especially when
> doing commands from a file, -f)? This is the default behaviour of the
> mysql client, except when we give it -f option ("force").
>
> The problem is, when restoring a dump, a failure at the some point might
> cause the subsequent commands to produce wrong results (e.g. I redefine
> a builtin function with a plruby function with different behaviour, but
> plruby failed to be installed due to wrong path. Thus the subsequent
> commands are executed using the builtin function which is not the
> expected one.) Furthermore, you can't check on psql exit code to see
> whether _any_ command was not successfully executed.
>
> Of course one should examine the full psql output after a restore
> anyway, and the option to exit immediately after an error can save time
> (especially for large dumps).
>
> --
> dave
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] lock conflicting with another
what does it mean when one lock mode conflicts with another like is says in the documentation? can somebody explain and maybe give an example. thanx, amir ---(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] lock conflicting with another
Amir Zicherman <[EMAIL PROTECTED]> writes: > what does it mean when one lock mode conflicts with another It means that a holder of the one lock type will block a requestor of the other lock type (on the same lockable object, of course). regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] error moving table to tablespace (8.0 beta win32 )
Rajesh Kumar Mallah wrote: > are you able to create new tables in the default tablespace ? Yes, permissions are ok. looks like a permission issue did you initdb a fresh folder see if the owner of $PGDATA/data/base is the user that running postmaster (usually postgres) . It works fine for me though initdb was run by windows installer Regds mallah test=# \d t_b Table "public.t_b" Column | Type | Modifiers +-+--- a | integer | Tablespace: "space" test=# ALTER TABLE t_b SET TABLESPACE pg_default; ALTER TABLE test=# SELECT version(); version -- PostgreSQL 8.0.0beta1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 (Mandrake Linux 10.0 3.3.2-6mdk) (1 row) test=# Maybe its only win32 version? Here what i tried: CREATE TABLE test (name varchar(100)); CREATE TABLE CREATE TABLESPACE test LOCATION 'C:\\tmp\\ts_test'; CREATE TABLESPACE \db List of tablespaces Name| Owner |Location +--+ pg_default | postgres | pg_global | postgres | test | postgres | C:/tmp/ts_test (3 rows) ALTER TABLE test SET TABLESPACE test; ALTER TABLE \d+ test Table "public.test" Column | Type | Modifiers | Description ++---+- name | character varying(100) | | Contains OIDs: yes Tablespace: "test" ALTER TABLE test SET TABLESPACE pg_default; ERROR: could not create relation 1663/317186/1317654: Permission denied select version(); version -- PostgreSQL 8.0.0beta1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.2.3 (mingw special 20030504-1) (1 row) Thanks and regards Christian ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Losing records when server hang
On Tue, 2004-08-10 at 19:30, lec wrote: > Chris Travers wrote: > The first hardware that got replaced was the SCSI controller. After > that there were still hardware "hanging" and eventually the system > engineers replaced the whole server and they weren't sure what the fault > was. The lost transactions happened not on the first server hang. I'm > more curious to know why the transactions in the middle got lost but the > last transaction was there. With questionable hardware (failing memory, scsi / raid controller, etc.) all failure modes are pretty much possible. PostgreSQL is good, but it can't make up for bad hardware. ---(end of broadcast)--- TIP 8: explain analyze is your friend
