Re: [SQL] postgres server crashes unexpectedly
Hi there, Sorry about the lack of information on the system. We're running fedora (not for sure what version though) core (whitebox). I did as you said and this is the result: DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.01 sec. INFO: "grp_member": moved 0 row versions, truncated 4 to 4 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: vacuuming "public.story_member" INFO: "story_member": found 603570 removable, 9903 nonremovable row versions in 43011 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 44 to 44 bytes long. There were 6139208 unused item pointers. Total free space (including removable row versions) is 323999824 bytes. 42732 pages are or will become empty, including 0 at the end of the table. 42958 pages containing 323999400 free bytes are potential move destinations. CPU 0.52s/0.18u sec elapsed 5.91 sec. INFO: index "fkx_story__story_member" now contains 9903 row versions in 17736 pages DETAIL: 64 index row versions were removed. 15219 index pages have been deleted, 15219 are currently reusable. CPU 0.29s/0.06u sec elapsed 26.88 sec. PANIC: corrupted item pointer: offset = 0, size = 0 server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: 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. Failed. !> !> I keep getting this error: 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. server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. What could be doing this? It just started out of the blue... I reindexed the index it mentioned and it seems to error out more... -Chadwick - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Chadwick Horn" <[EMAIL PROTECTED]> Cc: Sent: Monday, March 17, 2008 7:32 PM Subject: Re: [SQL] postgres server crashes unexpectedly "Chadwick Horn" <[EMAIL PROTECTED]> writes: PANIC: corrupted item pointer: offset = 0, size = 0 LOG: autovacuum process (PID 3037) was terminated by signal 6 Hmm ... the only instances of that error text are in PageIndexTupleDelete and PageIndexMultiDelete, so we can fairly safely say that you have a partially zeroed-out page in some index somewhere. If that's the only damage then you're in luck: you can recover by reindexing. What I'd do is turn off autovacuum and instead do a manual VACUUM VERBOSE to see where it crashes; then you could just reindex the one problem table instead of the whole database. You ought to look into why this happened, too. Since you've provided precisely 0 context about PG version or platform, it's hard to speculate about that ... regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] postgres server crashes unexpectedly
On Tue, 18 Mar 2008, Chadwick Horn wrote: Sorry about the lack of information on the system. We're running fedora (not for sure what version though) core (whitebox). This may not matter in the least bit, but have you tried running the DB on a real RHEL, or CentOS box? The kernel and libs on such a box would most likely be more stable than those on Fedora-based boxen... Cheers, -Josh -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] postgres server crashes unexpectedly
In all honesty, we're fairly "trapped" on the box we have due to the depths of corporate approvals required to get something new online. I would, most def, prefer to be on anything BUT this... - Original Message - From: "Joshua Kramerý" <[EMAIL PROTECTED]> To: "Chadwick Horn" <[EMAIL PROTECTED]> Cc: "Tom Lane" <[EMAIL PROTECTED]>; Sent: Tuesday, March 18, 2008 8:37 AM Subject: Re: [SQL] postgres server crashes unexpectedly On Tue, 18 Mar 2008, Chadwick Horn wrote: Sorry about the lack of information on the system. We're running fedora (not for sure what version though) core (whitebox). This may not matter in the least bit, but have you tried running the DB on a real RHEL, or CentOS box? The kernel and libs on such a box would most likely be more stable than those on Fedora-based boxen... Cheers, -Josh -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] postgres server crashes unexpectedly
"Chadwick Horn" <[EMAIL PROTECTED]> writes: > I keep getting this error: > Attempting reset: WARNING: terminating connection because of crash of another > server process It looks to me like psql is managing to start a new connection before the postmaster notices the crash of the prior backend and tells everybody to get out of town. Which is odd, but maybe not too implausible if your kernel is set up to favor interactive processes over background --- it'd likely think psql is interactive and the postmaster isn't. > What could be doing this? It just started out of the blue... I reindexed the > index it mentioned and it seems to error out more... If you reindexed only the last-mentioned index, then you reindexed the wrong thing; it presumably died on the next index of story_member. I'd reindex the whole table rather than guess which that is. You should also consider the not-zero probability that you have more than one corrupted index. Keep reindexing tables until you can get through a database-wide VACUUM. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] postgres server crashes unexpectedly
"Chadwick Horn" <[EMAIL PROTECTED]> writes: I keep getting this error: Attempting reset: WARNING: terminating connection because of crash of another server process It looks to me like psql is managing to start a new connection before the postmaster notices the crash of the prior backend and tells everybody to get out of town. Which is odd, but maybe not too implausible if your kernel is set up to favor interactive processes over background --- it'd likely think psql is interactive and the postmaster isn't. Is there a way to disable this or to make both interactive and/or background? What could be doing this? It just started out of the blue... I reindexed the index it mentioned and it seems to error out more... If you reindexed only the last-mentioned index, then you reindexed the wrong thing; it presumably died on the next index of story_member. I'd reindex the whole table rather than guess which that is. You should also consider the not-zero probability that you have more than one corrupted index. Keep reindexing tables until you can get through a database-wide VACUUM. I have VACUUM'd it until it's fibers are coming out. It seems to crash at various places (which, most likely, would be resolved if question #1 above is possible) and holds no consistancy. The error logs provide even fewer clues than the verbose output. -chadwick -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Create on insert a unique random number
When inserting a record is there a way to have postgres create a random number for a field such that it is unique? Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu
Re: [SQL] Create on insert a unique random number
> When inserting a record is there a way to have postgres create a > random number for a field such that it is unique? you could use oid Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Create on insert a unique random number
am Tue, dem 18.03.2008, um 9:43:01 -0700 mailte chester c young folgendes: > > > When inserting a record is there a way to have postgres create a > > random number for a field such that it is unique? > > > you could use oid No! No oid, it's deprecated. The solution: use serial. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Create on insert a unique random number
I created the following table:
create table xyz (
n serial,
abc character varying,
constraint n_pkey primary key (n));
Each time I do an insert:
insert into xyz(abc) values('adf6');
The field n is not random but is sequential. Is there something I
should do to make the serial number random?
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of A. Kretschmer
Sent: Tuesday, March 18, 2008 11:52 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Create on insert a unique random number
am Tue, dem 18.03.2008, um 9:43:01 -0700 mailte chester c young
folgendes:
>
> > When inserting a record is there a way to have postgres create a
> > random number for a field such that it is unique?
>
>
> you could use oid
No! No oid, it's deprecated. The solution: use serial.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Create on insert a unique random number
Campbell, Lance wrote:
I created the following table:
create table xyz (
n serial,
abc character varying,
constraint n_pkey primary key (n));
Each time I do an insert:
insert into xyz(abc) values('adf6');
The field n is not random but is sequential. Is there something I
should do to make the serial number random?
Thanks,
Why do you want a random number? Would a guid work?
Paul
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Create on insert a unique random number
On Tue, 18 Mar 2008 17:51:39 +0100 "A. Kretschmer" <[EMAIL PROTECTED]> wrote: > am Tue, dem 18.03.2008, um 9:43:01 -0700 mailte chester c young folgendes: > > > When inserting a record is there a way to have postgres create a > > > random number for a field such that it is unique? > > > > you could use oid > > No! No oid, it's deprecated. The solution: use serial. Not exactly random, is it? See http://archives.postgresql.org/pgsql-novice/2002-05/msg00198.php for a possible solution. Read the followups as well. There is an issue with collisions which will only get worse with time. I wonder though, what is the purpose of this? I suspect that this is either a homework problem or you may be attacking some real-world problem from the wrong angle. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Create on insert a unique random number
am Tue, dem 18.03.2008, um 12:03:31 -0500 mailte Campbell, Lance folgendes:
> I created the following table:
>
> create table xyz (
> n serial,
> abc character varying,
> constraint n_pkey primary key (n));
>
> Each time I do an insert:
>
> insert into xyz(abc) values('adf6');
>
> The field n is not random but is sequential. Is there something I
> should do to make the serial number random?
Why do you mean to need a random value? But you can use something like
md5(nextval('your_sequence')) as default. But i can't see an advantage.
>
>
> -Original Message-
Please, no top-posting. I'm reading from top to bottom.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Create on insert a unique random number
On Mar 18, 2008, at 1:03 PM, Campbell, Lance wrote: The field n is not random but is sequential. Is there something I should do to make the serial number random? Depending on your "randomness" need, you can alter the increment of the sequence so it changes by a different amount than "1" on every increment, though it will still be constant increment. You need to specify what the purpose of it being random is, then you might get more useful responses. Does it need to be random for some security purpose? If so, describe the level of security, specifically, against what threat are you defending? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Create on insert a unique random number
Thanks for all of your input. It appears that the best way to do this is to create a default random number in the primary id field in the table definition and then return that value after insert. If an exception occurs because of duplicates I will simple perform the same insert statement again. I doubt there would be many duplicate hits if I use a really large number. Why use a random number as a primary key? Security via obscurity. I build web applications for a living. In most of my applications it is preferable to use a random primary key. Why? Example: I built a web application called the Form Builder. It allows individuals to create web forms. After a user is done building their web form the tool provides a URL for the user to access the form. Obviously the URL has the random ID of the form in it. Most of the forms created with this tool can be accessed and filled out by the general public. So why not use a sequential number? So if I used a sequential number then a student or outside individual could easily change the number in the URL to see what other forms there are. It is not that they don't have access to the forms but they should not be messing with them if they really don't have a reason to. So by using a very large random number the users filling out a form cannot easily guess what another form ID is. Does that make sense? I have at least a dozen tools that I prefer to use this approach on. This is not a security approach. It is more about not giving obvious access to people that want to mess around. Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Vivek Khera Sent: Tuesday, March 18, 2008 12:57 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Create on insert a unique random number On Mar 18, 2008, at 1:03 PM, Campbell, Lance wrote: > The field n is not random but is sequential. Is there something I > should do to make the serial number random? Depending on your "randomness" need, you can alter the increment of the sequence so it changes by a different amount than "1" on every increment, though it will still be constant increment. You need to specify what the purpose of it being random is, then you might get more useful responses. Does it need to be random for some security purpose? If so, describe the level of security, specifically, against what threat are you defending? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] postgres server crashes unexpectedly
Chadwick Horn wrote: It looks to me like psql is managing to start a new connection before the postmaster notices the crash of the prior backend and tells everybody to get out of town. Which is odd, but maybe not too implausible if your kernel is set up to favor interactive processes over background --- it'd likely think psql is interactive and the postmaster isn't. Is there a way to disable this or to make both interactive and/or background? I'm not sure how applications tell the kernel whether they are interactive or background (or even if they do, at all), but you can set the kernel's preference for this in the kernel configuration. If you're not comfortable recompiling a new kernel, though, then you're out of luck. At any rate, you should look more thoroughly for problems with your database before blaming the kernel for something. Colin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Create on insert a unique random number
On Mar 18, 2008, at 1:40 PM, Campbell, Lance wrote: Thanks for all of your input. It appears that the best way to do this is to create a default random number in the primary id field in the table definition and then return that value after insert. If an exception occurs because of duplicates I will simple perform the same insert statement again. I doubt there would be many duplicate hits if I use a really large number. Why use a random number as a primary key? Security via obscurity. I build web applications for a living. In most of my applications it is preferable to use a random primary key. Why? Example: I built a web application called the Form Builder. It allows individuals to create web forms. After a user is done building their web form the tool provides a URL for the user to access the form. Obviously the URL has the random ID of the form in it. Most of the forms created with this tool can be accessed and filled out by the general public. So why not use a sequential number? So if I used a sequential number then a student or outside individual could easily change the number in the URL to see what other forms there are. It is not that they don't have access to the forms but they should not be messing with them if they really don't have a reason to. So by using a very large random number the users filling out a form cannot easily guess what another form ID is. Does that make sense? I have at least a dozen tools that I prefer to use this approach on. This is not a security approach. It is more about not giving obvious access to people that want to mess around. I'd say it makes total sense and we do much the same thing with unsubscribe links in the emails we push here. However, we keep the primary key based on sequences and for sent messages generate a random id using md5(now()::text). In a sense, then, we have "public" and "private" keys. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Create on insert a unique random number
On Mar 18, 2008, at 2:40 PM, Campbell, Lance wrote: Why use a random number as a primary key? Security via obscurity. I build web applications for a living. In most of my applications it is preferable to use a random primary key. Why? Don't expose the actual ID to the end user; only expose a reversible encrypted form of it. We use a relatively simple hash + check character. If you have several examples of it, you can reverse engineer it, but the casual "hacker" is easily thwarted. You can use stronger encryption on the number when exposed to end users if you need. You're making your DB overly complex. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Create on insert a unique random number
At 11:58 AM 3/18/2008, [EMAIL PROTECTED] wrote:
Date: Tue, 18 Mar 2008 13:40:42 -0500
From: "Campbell, Lance" <[EMAIL PROTECTED]>
To: "Vivek Khera" <[EMAIL PROTECTED]>,
Subject: Re: Create on insert a unique random number
Message-ID:
<[EMAIL PROTECTED]>
Thanks for all of your input. It appears that the best way to do this
is to create a default random number in the primary id field in the
table definition and then return that value after insert. If an
exception occurs because of duplicates I will simple perform the same
insert statement again. I doubt there would be many duplicate hits if
I
use a really large number.
[snip]
I built a web application called the Form Builder. It allows
individuals to create web forms. After a user is done building their
web form the tool provides a URL for the user to access the form.
Obviously the URL has the random ID of the form in it. Most of the
forms created with this tool can be accessed and filled out by the
general public.
[snip]
Hi Lance,
I think I "get you" as a fellow web systems (aka middleware) guy. My
opinion is that the use of a "sparse index" is totally reasonable for
the purpose you describe. But I would argue that you could take it a
little further in implementation that might keep your db design sane
while still giving you the sparse index function on the front-end.
1) Create a second field (as someone recommend on this list) that is an
MD5 of your primary key. Use that as your "accessor" index from the web
application. But keep the primary key as an integer serial, so that it
works as expected, and you can build relations normally. I think in the
end you'll be happier with this method than messing around with a
custom primary key system.. You can build a trigger that generates the
MD5 hash every time a record is created (or you can do it in your ORM
layer in the web app).
2) Also, (but OT) put a monitor on your weblogs to look for "404"
errors ("page not found" for the sql-only people here). This will
supplement your sparse index by detecting people who are scanning your
sparse index space and generating lots of "misses."
Hope that helps,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Create on insert a unique random number
On Tue, 18 Mar 2008 13:40:42 -0500 "Campbell, Lance" <[EMAIL PROTECTED]> wrote: > Why use a random number as a primary key? Security via obscurity. Something with very short shelf life but... > I build web applications for a living. In most of my applications it is > preferable to use a random primary key. Why? I understand why you might need a random field. My question is, why does it have to be the primary key? I'm also not sure why it has to be unique. You can always base the URL on both the primary key and the security field. Now you don't need to worry about collisions. In addition the serial number can be a public reference to the record. Off-topic but related, funny story, I was once in charge of a medium sized ISP and some suit came to me and suggested that for extra security we should not let users pick passwords that already existed in the system. My response was "So the error message should be that someone in the system already has the password that you tried to use?" -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Create on insert a unique random number
On Tue, 18 Mar 2008 12:23:35 -0700 Steve Midgley <[EMAIL PROTECTED]> wrote: > 1) Create a second field (as someone recommend on this list) that is an > MD5 of your primary key. Use that as your "accessor" index from the web I strongly disagree for three reasons. First, if you are going to generate a key then don't store it. Just generate it every time. Second, don't generate it based on a known field. You may think that it is secure but what if you private key is compromised? Do you then change everyone's security code? Third, what if one person's code is compromised? If it is based on a calculation then you can't change that one person's security code. Generate a random number and store that. You will be much happier when something goes wrong and something always goes wrong. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] pg_dump using SQL
Hi everyone Is there any way to do what pg_dump does?, I mean, get the structure of a table in a database (ex: CREATE TABLE ...) thanks in advance.
Re: [SQL] Create on insert a unique random number
At 12:36 PM 3/18/2008, D'Arcy J.M. Cain wrote: On Tue, 18 Mar 2008 12:23:35 -0700 Steve Midgley <[EMAIL PROTECTED]> wrote: > 1) Create a second field (as someone recommend on this list) that is an > MD5 of your primary key. Use that as your "accessor" index from the web I strongly disagree for three reasons. First, if you are going to generate a key then don't store it. Just generate it every time. Second, don't generate it based on a known field. You may think that it is secure but what if you private key is compromised? Do you then change everyone's security code? Third, what if one person's code is compromised? If it is based on a calculation then you can't change that one person's security code. Generate a random number and store that. You will be much happier when something goes wrong and something always goes wrong. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves Hi D'Arcy, I'm not clear on your concern here - an MD5 hash doesn't have a private key that can be compromised, afaik. It's a one way hash. I don't see much difference between making an MD5 of the primary key and generating a random number for the "public primary key", except that you shouldn't get index collisions with the MD5 method (whereas eventually you will with a random number, though of course using a GUID would eliminate that concern for practical purposes). The issue raised by the OP, I believe, is not about security of the primary key # itself or its ability to provide unauthorized access to the underlying records. The system in question protects its records from unauthorized access already. The issue is about creating an index into a sparse hash so that each record is somewhat randomly located in a sparse hash "index space". (One valid reason to do this would be if you wanted to hide the total number of records in your table from competitors or customers). (Just for reference of my view on the problem: http://en.wikipedia.org/wiki/Hash_table) Whether SHA-1 or MD5, I think the point is that if you don't care about speed in generating the hash index (which the OP doesn't apparently), hash indexing via an encryption algorithm will ensure that the hash index is relatively free of "clustering" - which as I understand it, is the point of this exercise. Encryption as a hash index generator is imperfect for sure, as the Wikipedia article goes at length to discuss, but from my perspective it "does the job" - at least as far as the OP describes it (or I understood it!). [smile] I may be way off here of course, and I appreciate the input - any thoughts? Steve -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_dump using SQL
> Is there any way to do what pg_dump does?, I mean, get the structure of a table in a database (ex: CREATE TABLE ...) Turn Query Logging on in postgresql.conf then see what queries are executed by pgAdmin or similar when you run it? THINK BEFORE YOU PRINT - Save paper if you don't really need to print this ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments e-mail. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_dump using SQL
"Phillip Smith" <[EMAIL PROTECTED]> writes: >> Is there any way to do what pg_dump does?, I mean, get the structure of a >> table in a database (ex: CREATE TABLE ...) > Turn Query Logging on in postgresql.conf then see what queries are executed > by pgAdmin or similar when you run it? But note that these queries are moving targets: they frequently change from one PG version to the next. By far the best answer, if you can use it, is to invoke pg_dump itself as a subprocess. Something like "pg_dump -s -t mytable ..." for instance. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
