Martijn van Oosterhout wrote:
Not quite, a single index entry needs to point to any number of rows,
which may or may not be visible depending on your transaction, so they
form a sort of linked list. But indeed, not terribly useful for your
purpose...
This make's sense, I keep forgetting the vers
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Which reminds me that you wanted to make VACUUM FULL do the equivalent
> of a REINDEX instead of retail deletion of index entries ... is that
> still the idea? Would it do that always, or only under certain
> conditions?
It's still on the to-do list.
Alvaro Herrera wrote:
> On Sat, Jan 15, 2005 at 04:21:24PM -0500, Tom Lane wrote:
> > "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > > Out of curiosity, what clears out the old index tuples? Vacuum?
> >
> > Right.
>
> Which reminds me that you wanted to make VACUUM FULL do the equivalent
> of a R
On Sat, Jan 15, 2005 at 04:21:24PM -0500, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > Out of curiosity, what clears out the old index tuples? Vacuum?
>
> Right.
Which reminds me that you wanted to make VACUUM FULL do the equivalent
of a REINDEX instead of retail deletion of i
On Sat, Jan 15, 2005 at 04:00:19PM -0500, Tom Lane wrote:
> Martijn van Oosterhout writes:
> > On Sat, Jan 15, 2005 at 07:10:48PM +0100, Bo Lorentsen wrote:
> >> Hmm, so a data row update also update the CTID in all indexes, too. I=20
> >> see what you mean !
>
> > Not quite, a single index entry
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> Out of curiosity, what clears out the old index tuples? Vacuum?
Right.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http:/
Martijn van Oosterhout writes:
> On Sat, Jan 15, 2005 at 07:10:48PM +0100, Bo Lorentsen wrote:
>> Hmm, so a data row update also update the CTID in all indexes, too. I=20
>> see what you mean !
> Not quite, a single index entry needs to point to any number of rows,
> which may or may not be visib
On Sat, Jan 15, 2005 at 07:10:48PM +0100, Bo Lorentsen wrote:
> >Using the CTID, which locates the physical tuple as (block,num). When
> >you update a tuple, or vacuum moves it its CTID will change, so it's
> >not terribly useful from a user's point of view.
> >
> Hmm, so a data row update also upd
Uh, sorry, my mistake !
I had put SERIAL instead of an INTEGER in the table definition !
You just removed a bug in my schema ;)
On Sat, Jan 15, 2005 at 09:02:12AM +0100, PFC wrote:
As a sidenote, I have a table with a primary key which is not a
sequence, and this query displa
Martijn van Oosterhout wrote:
But where in the documentation did you see anything saying that they
were unique? I imagine you just inferred that from somewhere. I'm not
sure where the documentation should be changed since nowhere actually
recommends them in any way.
Hmm, how about as a comment n
On Sat, Jan 15, 2005 at 05:53:08PM +0100, Bo Lorentsen wrote:
> Michael Fuhr wrote:
>
> >The view assumes single-column primary keys defined as SERIAL types.
> >
> is this the "c2.relkind = 'S'" in the view ?
That restricts the view to show only dependent objects that are
sequences ('S'). Defini
On Sat, Jan 15, 2005 at 05:53:08PM +0100, Bo Lorentsen wrote:
> Michael Fuhr wrote:
> >The view assumes single-column primary keys defined as SERIAL types.
>
> is this the "c2.relkind = 'S'" in the view ?
No, that means the pg_class entry is a sequence.
--
Alvaro Herrera (<[EMAIL PROTECTED]>)
"
PFC wrote:
As a sidenote, I have a table with a primary key which is not a
sequence, and this query displays the non-existing sequence name. It
would be easy to check if the sequence exists (yet another join !),
only display sequences that exist ;)...
Hmm, I just tried the same, and got a
On Sat, Jan 15, 2005 at 05:11:16PM +0100, Bo Lorentsen wrote:
> Alvaro Herrera wrote:
> >Most system catalogs use OIDs as primary keys. So they cannot just
> >disappear. But on user tables, there's not a lot of use for them IMHO.
> >
> Ok, I think it is about time it is stated more clearly in the
Michael Fuhr wrote:
Here's a first attempt at a view that shows tables and their primary
key columns and sequences. I chose a view instead of a function
because a view shows everything in the database with a single query,
which simplifies visual examination of the results. Modify it or
convert it
Alvaro Herrera wrote:
Most system catalogs use OIDs as primary keys. So they cannot just
disappear. But on user tables, there's not a lot of use for them IMHO.
Ok, I think it is about time it is stated more clearly in the documentation.
There's no internal row id on Postgres; having one would
On Sat, Jan 15, 2005 at 09:02:12AM +0100, PFC wrote:
>
> As a sidenote, I have a table with a primary key which is not a
> sequence, and this query displays the non-existing sequence name. It
> would
> be easy to check if the sequence exists (yet another join !), only display
> se
I've done only trivial testing, so if anybody finds a situation
where the view fails (taking the above assumption into account)
then please describe it.
Nice !
As a sidenote, I have a table with a primary key which is not a sequence,
and this query displays the non-existing sequence name. It w
On Fri, Jan 14, 2005 at 06:39:25PM -0600, Jim C. Nasby wrote:
>
> If you do manage to write a function that will do this I hope you can
> share it with the community. IMHO PostgreSQL could do with more
> functions for querying the system catalogs.
Here's a first attempt at a view that shows table
On Fri, Jan 14, 2005 at 05:10:10PM -0300, Alvaro Herrera wrote:
> On Fri, Jan 14, 2005 at 12:20:50PM -0700, Michael Fuhr wrote:
> > On Fri, Jan 14, 2005 at 07:44:18PM +0100, Bo Lorentsen wrote:
> > > Alvaro Herrera wrote:
> > >
> > > >You can create a function to get the sequence name attached to
It's not very hard to do. I just got rid them. It took me about a day. Our
application is an X-Windows front end written is C. I wrote a function to
return the next value of the serial key for any table. Here is the select
statement buitl with sprintf:
"SELECT relname FROM pg_class WHERE relkind
On Sat, Jan 15, 2005 at 12:06:41AM +0100, Bo Lorentsen wrote:
> Ok, Tom told me about the same :-( But why are oid's still in PG, that
> are they good for ? Will there be a real unique row id, like there is in
> Oracle, or will this be keept as an internal value only ?
Most system catalogs use
On Sat, Jan 15, 2005 at 12:06:41AM +0100, Bo Lorentsen wrote:
> Martijn van Oosterhout wrote:
> >It means using OIDs as you described has very well known problems and
> >they will break on you eventually. You can mitigate the damage by
> >creating a UNIQUE index on the oid column but you'd better b
Michael Fuhr wrote:
See the "System Catalogs" chapter in the documentation.
Ok, I think I will compile all the given information in this thread, to
make a new and more non oid'ish solution, as the dataset I manage are
going to grow quite a lot :-)
If you run "psql -E" you'll see the queries t
Alvaro Herrera wrote:
Yeah, though things get hairy that way because you have to peek at
pg_attribute to match the objsubid in pg_depend; and self-join pg_class
to get to the index itself. Not sure if it all can be done in a single
query.
Sounds like my task, to make an oid free insert/select,
Michael Fuhr wrote:
The PostgreSQL documentation discourages the use of OIDs for primary
keys. For example, the "Object Identifier Types" section in the
"Data Types" chapter says:
...
Thanks for taking you the time to snip this together, I think I will try
to find a way to find the propper pri
Martijn van Oosterhout wrote:
It means using OIDs as you described has very well known problems and
they will break on you eventually. You can mitigate the damage by
creating a UNIQUE index on the oid column but you'd better be sure your
application can handle the side-effects.
Ok, Tom told me a
On Fri, Jan 14, 2005 at 12:20:50PM -0700, Michael Fuhr wrote:
> On Fri, Jan 14, 2005 at 07:44:18PM +0100, Bo Lorentsen wrote:
> > Alvaro Herrera wrote:
> >
> > >You can create a function to get the sequence name attached to a table.
> > >Of course, you should take into account the fact that there
On Fri, Jan 14, 2005 at 07:44:18PM +0100, Bo Lorentsen wrote:
> Alvaro Herrera wrote:
>
> >You can create a function to get the sequence name attached to a table.
> >Of course, you should take into account the fact that there could be
> >more than one (two serial fields in a table are rare but not
On Fri, Jan 14, 2005 at 07:39:16PM +0100, Bo Lorentsen wrote:
> But, does this mean that the oid sollution I have decriped (and
> implimentet) have some unknown problems, or will oid's become obsolete
> in the near future ?
It means using OIDs as you described has very well known problems and
th
On Fri, Jan 14, 2005 at 07:39:16PM +0100, Bo Lorentsen wrote:
>
> But, does this mean that the oid sollution I have decriped (and
> implimentet) have some unknown problems, or will oid's become obsolete
> in the near future ?
The PostgreSQL documentation discourages the use of OIDs for primary
Tom Lane wrote:
The thing you have to worry about is the possibility of duplicate OIDs
once your DB has been running long enough for the OID counter to wrap
around (2^32 OIDs). You should make sure that index is specifically
declared as UNIQUE, so that any attempt to insert a duplicate OID will
fa
Alvaro Herrera wrote:
You can create a function to get the sequence name attached to a table.
Of course, you should take into account the fact that there could be
more than one (two serial fields in a table are rare but not
impossible), but if your tables have only one sequence you should be OK.
Michael Fuhr wrote:
You could query the system catalogs for the table's primary key,
either on the client side or in a server-side function. The
pg_attrdef table even has the default value's nextval() expression
with the sequence name, which could be converted into a currval()
call.
This is not
On Fri, Jan 14, 2005 at 11:47:25AM +0100, Bo Lorentsen wrote:
> Michael Fuhr wrote:
>
> >PostgreSQL 8.0 will have a pg_get_serial_sequence() function that
> >returns the sequence name for a particular column so you don't have
> >to construct it. This is useful when a table or column has been
> >r
Bo Lorentsen <[EMAIL PROTECTED]> writes:
> I use normal tabel ID (SERIAL and BIGSERIAL) all over the place for FK
> constaints, but I use OID in one special situation. When I insert a
> single row into a table, I like my low level code to be kompatible with
> mysql ( mysql_insert_id ), and fetch
On Fri, Jan 14, 2005 at 11:39:54AM +0100, Bo Lorentsen wrote:
> Thanks, but this demands you to have the table and id_column name in
> your hand, and I don't right now.
You can create a function to get the sequence name attached to a table.
Of course, you should take into account the fact that t
Michael Fuhr wrote:
PostgreSQL 8.0 will have a pg_get_serial_sequence() function that
returns the sequence name for a particular column so you don't have
to construct it. This is useful when a table or column has been
renamed, in which case the above will probably break.
Quite nice but not what
Christian Kratzer wrote:
why should your application not want to know about the metadata of it's
own tables ? That sounds quite strange when you think about it.
Well, the ideer is to be compatible with mysql at the same level in the
code. This works nicely, as I have descriped, but I am concerned
On Fri, Jan 14, 2005 at 10:32:18AM +0100, Christian Kratzer wrote:
> $query = sprintf("SELECT currval('%s_%s_seq') AS
> id",$this->table,$this->id_column);
PostgreSQL 8.0 will have a pg_get_serial_sequence() function that
returns the sequence name for a particular column so you don't have
to con
Hi,
On Fri, 14 Jan 2005, Bo Lorentsen wrote:
Michael Glaesemann wrote:
You can use currval() to get the sequence value that was pulled from your
insert. You can check the documentation for usage, as well as searching
the archives for discussions of using OIDs as part of your database logic.
I kno
Michael Glaesemann wrote:
You can use currval() to get the sequence value that was pulled from
your insert. You can check the documentation for usage, as well as
searching the archives for discussions of using OIDs as part of your
database logic.
I know this, but i like not to know anything abou
On Jan 14, 2005, at 16:03, Bo Lorentsen wrote:
Now, are there any danger in using this method ? And if there is, how
can I do this trick without knowing the layout of the table I insert
into ?
You can use currval() to get the sequence value that was pulled from
your insert. You can check the doc
43 matches
Mail list logo
