headers
Tom Lane | 1 Apr 2005 01:33
Picon

Re: truncate/create slowness

"Julian Scarfe" <julian <at> avbrief.com> writes:
> Do you have any rules of thumb for deciding when a pg_dumpall/restore is 
> likely to be faster than a vacuum full?  Or perhaps more straightforwardly, 
> how would you expect the time required for a vacuum full to scale with pages 
> used and rows in the table?

There is a factor that's proportional to the number of tuples deleted,
and a bigger factor that's proportional to the number of tuples moved
while trying to compact the table.  If you've got a seriously bloated
table then it's fairly likely that *all* the surviving tuples will get
moved because none of them are near the start of the table already :-(

Having said that, though, a vacuum full and reindex on pg_class and
pg_attribute will certainly solve Steve's problem faster than a dump
and reload, simply because there's not much stuff in those catalogs
compared to any reasonably-sized user tables.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo <at> postgresql.org)
Permalink | Reply | 0 comments |
headers
Jamie Deppeler | 1 Apr 2005 02:05
Picon

getGeneratedKeys()

Hi to all,

I have one problem with PostgreSQL and Java. I have a table with Primary 
key(serial) field, but after I insert a record i am unable to retrieve 
this value.  I have tried getGeneratedKeys() and i get nothing returned.

There is another method to retrieve this field?

thanks

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org
Permalink | Reply | 0 comments |
headers
Joshua D. Drake | 1 Apr 2005 02:33
Favicon

Re: getGeneratedKeys()

On Fri, 2005-04-01 at 10:05 +1000, Jamie Deppeler wrote:
> Hi to all,
> 
> I have one problem with PostgreSQL and Java. I have a table with Primary 
> key(serial) field, but after I insert a record i am unable to retrieve 
> this value.  I have tried getGeneratedKeys() and i get nothing returned.

I am not a Java programmer but normally I would use select currval
('sequencename');

> 
> There is another method to retrieve this field?
> 
> thanks
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org
--

-- 
Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564
Custom programming, 24x7 support, managed services, and hosting
Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG
Reliable replication, Mammoth Replicator - http://www.commandprompt.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
(Continue reading)

Permalink | Reply | 0 comments |
headers
Andrew Chambers | 1 Apr 2005 02:46
Picon
Favicon

your thoughts on a crazy idea please

I came across an old RDBM called Business System 12 
(http://www.mcjones.org/System_R/bs12.html) a few days ago.  It seemed 
to have a much simpler method of specifying queries - more similar in 
style to relation algebra than SQL.  For example, some example code 
might look like this.

view =                 join(rel_1, rel_2)    --assertains join criteria 
based on primary keys
filtered_view =    select(condition_list, view)
result =               project(attribute_list, filtered_view)

Ignoring for a minute that SQL is the accepted method of talking to 
databases, and also acknowledging that SQL may be more expressive than 
relational algebra, I wondered if it would be possible to extend 
Postgresql to allow non-sql interfaces to simple database services. 

I suppose it would be possible to convert statements like those above 
into their SQL equivalent before passing them to an existing driver, but 
from reading the docs, it seems that dbmses usually do these types of 
operations once they have parsed the SQL anyway.

I'd be interested to hear your thoughts on the subject whatever they 
are.  Critical comments are particularly welcome (they might be quite 
useful for getting this stupid idea out of my head ;-)).

Regards,
Andy

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
(Continue reading)

Permalink | Reply | 1 comment |
headers
Dann Corbit | 1 Apr 2005 02:54

Re: your thoughts on a crazy idea please


-----Original Message-----
From: pgsql-general-owner <at> postgresql.org
[mailto:pgsql-general-owner <at> postgresql.org] On Behalf Of Andrew Chambers
Sent: Thursday, March 31, 2005 4:46 PM
To: postgres
Subject: [GENERAL] your thoughts on a crazy idea please

I came across an old RDBM called Business System 12 
(http://www.mcjones.org/System_R/bs12.html) a few days ago.  It seemed 
to have a much simpler method of specifying queries - more similar in 
style to relation algebra than SQL.  For example, some example code 
might look like this.

view =                 join(rel_1, rel_2)    --assertains join criteria 
based on primary keys
filtered_view =    select(condition_list, view)
result =               project(attribute_list, filtered_view)

Ignoring for a minute that SQL is the accepted method of talking to 
databases, and also acknowledging that SQL may be more expressive than 
relational algebra, I wondered if it would be possible to extend 
Postgresql to allow non-sql interfaces to simple database services. 
===================================================================>>
If you use libpq then you can write any sort of interface to PostgreSQL
that your heart might desire.
<<===================================================================

I suppose it would be possible to convert statements like those above 
into their SQL equivalent before passing them to an existing driver, but
(Continue reading)

Permalink | Reply | 0 comments |
headers
Guy Rouillier | 1 Apr 2005 02:54
Favicon

Re: Debugging deadlocks

Alvaro Herrera wrote:
> 
> Now this can't be applied right away because it's easy to run "out of
> memory" (shared memory for the lock table).  Say, a delete or update
> that touches 10000 tuples does not work.  I'm currently working on a
> proposal to allow the lock table to spill to disk ...   

While not always true, in many cases the cardinality of the referenced
(parent) table is small compared to that of the referencing (child)
table.  Does locking require a separate lock record for each tuple in
the child table, or just one for each tuple in the parent table with a
reference count?  For example, the scenario I started this thread with
had two child tables referencing rows in a common parent table.  For a
given parent tuple, a single "prevent write" lock with a reference count
would seem sufficient.

--

-- 
Guy Rouillier

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Permalink | Reply | 0 comments |
headers
Chandra Sekhar Surapaneni | 1 Apr 2005 02:47

Help with converting hexadecimal to decimal

Hi All,
  Is there a built in function which works exactly the opposite way as
to_hex(). 
I basically want to convert a a hexadecimal to a decimal. 
Thanks

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Permalink | Reply | 3 comments |
headers
Alvaro Herrera | 1 Apr 2005 03:31
Picon
Picon
Favicon

Re: [GENERAL] Debugging deadlocks

On Thu, Mar 31, 2005 at 06:54:31PM -0600, Guy Rouillier wrote:
> Alvaro Herrera wrote:
> > 
> > Now this can't be applied right away because it's easy to run "out of
> > memory" (shared memory for the lock table).  Say, a delete or update
> > that touches 10000 tuples does not work.  I'm currently working on a
> > proposal to allow the lock table to spill to disk ...   
> 
> While not always true, in many cases the cardinality of the referenced
> (parent) table is small compared to that of the referencing (child)
> table.  Does locking require a separate lock record for each tuple in
> the child table, or just one for each tuple in the parent table with a
> reference count?

Just one.  (LOCALLOCK, which is private to each backend, stores how many
times we hold a lock.)

I just realized we not only need to be able to spill LOCK struct to
disk, but also PROCLOCK ... am I right?

--

-- 
Alvaro Herrera (<alvherre[ <at> ]dcc.uchile.cl>)
La web junta la gente porque no importa que clase de mutante sexual seas,
tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con
ciervos incendiándose", y el computador dirá "especifique el tipo de ciervo"
(Jason Alexander)

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
(Continue reading)

Permalink | Reply | 0 comments |
headers
Joseph Shraibman | 1 Apr 2005 03:57

table permissions

Is there a function I can call to see if the current user has 
permissions on a certain table?

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Permalink | Reply | 1 comment |
headers
Michael Fuhr | 1 Apr 2005 04:18
Favicon

Re: table permissions

On Thu, Mar 31, 2005 at 08:57:17PM -0500, Joseph Shraibman wrote:
>
> Is there a function I can call to see if the current user has 
> permissions on a certain table?

See "System Information Functions" (or "Miscellaneous Functions")
in the "Functions and Operators" chapter of the documentation.
Here's a link to the latest version:

http://www.postgresql.org/docs/8.0/interactive/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE

--

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Permalink | Reply | 0 comments |

Gmane