Re: [HACKERS] missing pg_clog files ?

[Patrick Welche]

On Mon, Sep 22, 2003 at 02:21:43PM -0400, Alvaro Herrera wrote:
> On Mon, Sep 22, 2003 at 05:03:28PM +0100, Patrick Welche wrote:
> > On Mon, Sep 22, 2003 at 11:33:30AM -0400, Tom Lane wrote:
> > > Patrick Welche <[EMAIL PROTECTED]> writes:
> > > > I hope I guessed the right syntax...
> > > > % pg_filedump -R 71716 data/base/17148/283342
> > > 
> > > Yes, but this doesn't give all the available info.  Add -i and -f
> > > options.  A plain -d dump might be interesting too.
> > 
> > Indeed, the plain -d dump says that I have a chunk of /var/mail/prlw1
> > in 1000-13ff. No wonder postgres complained!
> 
> For the record, what filesystem is this on?  Is it ReiserFS by any
> chance?

Nope, ffs with soft dependencies, on a 1 month old IDE drive (read not
yet known good...) Hmm maybe I'd better ask over on the NetBSD list,
though I think my kernel is from just before ide rototill which was actually
on atapi rather than straight ide AFAICT, and certainly before gcc 3.3.1,
so the duff hardware scenario seems best..

Cheers,

Patrick

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

Re: [HACKERS] Improving REINDEX for system indexes (long)

[Gaetano Mendola]

Hiroshi Inoue wrote:
> instead. Because it was impossible to make REINDEX transaction-safe
> then, such flag was needed to suppress inconsistency as less
> as possible.

This mean that the actual REINDEX is not transaction-safe ?


Regards
Gaetano Mendola


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

   http://archives.postgresql.org

Re: [HACKERS] Improving REINDEX for system indexes (long)

[Hiroshi Inoue]

> -Original Message-
> From: Gaetano Mendola [mailto:[EMAIL PROTECTED] 
> 
> Hiroshi Inoue wrote:
> > instead. Because it was impossible to make REINDEX transaction-safe
> > then, such flag was needed to suppress inconsistency as less
> > as possible.
> 
> This mean that the actual REINDEX is not transaction-safe ?

No.
It was not transaction-safe long time ago.

regards,
Hiroshi Inoue


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[HACKERS] free(3)-ing variables in pg_dump

[Andreas Joseph Krogh]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi.

I'm trying to implement functionallity to dump multiple tables with multiple 
"-t " options. While digging in the source for pg_dump I see that 
many local static variables are not freed( with free(3)). Is this lazy 
programming because pg_dump is its own process  where the kernel takes care 
of cleaning up, so you don't bother to do it for some of the variables? I'm 
malloc'ing some structs to build a list over tables which are marked for 
dumping. Shall I bother to free(3) them?

- -- 
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Managing Director, Senior Software Developer
OfficeNet AS

- - Writing software is more fun than working.

gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQE/cE2SUopImDh2gfQRAhOxAJ0Wr7s98ufN4BEckpVem/tFfekIwQCghS+3
8x/TV1Oqx++ywYDyOJxQSCU=
=uKgq
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 8: explain analyze is your friend

[HACKERS] Question on adding new indexes to Postgresql

[Zhen Yang]

Hello, I am posting to ask for some help and suggestions.

For a project I am doing in university, I need to add indexing
mechanisms to the Postgresql database (R+ tree, bitmap indexes).
Version of Postgresql is 7.3.3. I would like to ask how I can
integrate the structures and algorithms for the indexes into
the Postgresql DB. What are the C interface functions in Postgresql
to add indexes? Do I need to handle things like memory buffer
management, disk storage management when implementing the indexes?
Thanks in advance.


Sincerely,
Zhen Yang

__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] free(3)-ing variables in pg_dump

[Andrew Dunstan]

Andreas Joseph Krogh wrote:

Hi.

I'm trying to implement functionallity to dump multiple tables with multiple 
"-t " options. 

excellent.

While digging in the source for pg_dump I see that 
many local static variables are not freed( with free(3)). Is this lazy 
programming because pg_dump is its own process  where the kernel takes care 
of cleaning up, so you don't bother to do it for some of the variables? I'm 
malloc'ing some structs to build a list over tables which are marked for 
dumping. Shall I bother to free(3) them?

 

I don't think it's lazy, probably just a product of the programmer's 
awareness that little would be gained by it. Relying on the OS to clean 
up for you is perfectly valid in a shortlived program unless you get a 
major problem with memory leaks.

"If it ain't broke, don't fix it" would be my take.

(If this is not the consensus I'm going to have some more work to do in 
the C port of initdb I'm working on, which is about one third done :-)

cheers

andrew

---(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: [HACKERS] free(3)-ing variables in pg_dump

[Andreas Joseph Krogh]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Tuesday 23 September 2003 16:25, Andrew Dunstan wrote:
> Andreas Joseph Krogh wrote:
> >Hi.
> >
> >I'm trying to implement functionallity to dump multiple tables with
> > multiple "-t " options.
>
> excellent.
>
> >While digging in the source for pg_dump I see that
> >many local static variables are not freed( with free(3)). Is this lazy
> >programming because pg_dump is its own process  where the kernel takes
> > care of cleaning up, so you don't bother to do it for some of the
> > variables? I'm malloc'ing some structs to build a list over tables which
> > are marked for dumping. Shall I bother to free(3) them?
>
> I don't think it's lazy, probably just a product of the programmer's
> awareness that little would be gained by it. Relying on the OS to clean
> up for you is perfectly valid in a shortlived program unless you get a
> major problem with memory leaks.

I didn't mean lazy as in bad - I ment lazy as in just what you pointed out: 
little would be gained by it, so why bother.

> "If it ain't broke, don't fix it" would be my take.
>
> (If this is not the consensus I'm going to have some more work to do in
> the C port of initdb I'm working on, which is about one third done :-)

:-)

- -- 
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Managing Director, Senior Software Developer
OfficeNet AS

- - Writing software is more fun than working.

gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQE/cF3DUopImDh2gfQRAtR4AJ499CK4QcGO9Dc0Wato46/wZMxZ/wCaApSP
463Z/DfsEBtUvQ50h/osKAc=
=eEqD
-END PGP SIGNATURE-

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

   http://archives.postgresql.org

Re: [HACKERS] Question on adding new indexes to Postgresql

[Tom Lane]

Zhen Yang <[EMAIL PROTECTED]> writes:
> What are the C interface functions in Postgresql to add indexes?

You will need to study the source code for the existing index types.
There is not much documentation other than the sources for the index
access method APIs.

regards, tom lane

---(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: [HACKERS] free(3)-ing variables in pg_dump

[Tom Lane]

Andreas Joseph Krogh <[EMAIL PROTECTED]> writes:
> Shall I bother to free(3) them?

No.  They need to live for the entire pg_dump run, so there's no point
in writing code to free them.

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

[HACKERS] pg_dump doesn't dump binary compatible casts

[Jan Wieck]

The offending source code is in pg_dump.c line 3953, where at the lack 
of an underlying conversion function and thus no clear namespace 
relationship pg_dump simply ignores the cast.

IMHO a binary compatible cast should be dumped if one or both namespaces 
of the underlying data types is included in the dump.

I classify this problem as a bug. Objections?

Jan

--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] pg_dump doesn't dump binary compatible casts

[Tom Lane]

Jan Wieck <[EMAIL PROTECTED]> writes:
> I classify this problem as a bug. Objections?

The question is not whether it is a bug, the question is what is correct
behavior instead.

> IMHO a binary compatible cast should be dumped if one or both namespaces 
> of the underlying data types is included in the dump.

That would just replace one bug with another (viz, failure to restore
if one of the datatypes wasn't included in the dump).  Also, what of
user-defined casts between two system datatypes?  pg_catalog is never
considered part of the dump AFAIR.

The first idea that came to mind is to dump the cast if both underlying
types are either system types or included in the dump.  But I think that
would end up dumping built-in binary casts, which is no good either.
I hate to think of looking at the cast's OID vs. lastsysoid to decide
if it was a built-in cast, but maybe there's no other way.

Ideas anyone?

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] [GENERAL] Can't Build 7.3.4 on OS X

[Tom Lane]

Marko Karppinen <[EMAIL PROTECTED]> writes:
> While we're on a Mac-related note, I managed to compile PostgreSQL on  
> Mac OS X 10.2 Jaguar with two-level namespace support.  
>  TwoLevelNamespaces.html> This is quite useful, so I hope you can look  
> into incorporating the changes.

> http://www.markokarppinen.com/pg-two-level-namespace.diff

Three immediate questions:

1.  Doesn't this break backward compatibility with Darwin 1.0-1.2?
(If so, does anyone care anymore?)

2.  Why is the postgres executable added to LINK.shared?  Doesn't this
break building shlibs that are not intended to be linked into the
backend (eg libpq)?

3.  What's with the WITHOUT_DARWIN_BUNDLE_LOADER hack for ecpg?

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Improving REINDEX for system indexes (long)

[Gaetano Mendola]

Hiroshi Inoue wrote:
Gaetano Mendola [mailto:[EMAIL PROTECTED]  wrote:
Hiroshi Inoue wrote:

instead. Because it was impossible to make REINDEX transaction-safe
then, such flag was needed to suppress inconsistency as less
as possible.
This mean that the actual REINDEX is not transaction-safe ?


No.
It was not transaction-safe long time ago.
Anyway I think there is a nasty bug somewhere, see my last posts
about "duplication primary key".
Regards
Gaetano Mendola








---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [HACKERS] [GENERAL] Can't Build 7.3.4 on OS X

[Marko Karppinen]

On 23.9.2003, at 19:01, Tom Lane wrote:
1.  Doesn't this break backward compatibility with Darwin 1.0-1.2?
(If so, does anyone care anymore?)
No. The two-level namespace feature was added in 10.1, and the
-flat_namespace hack was included in pgsql to unbreak the compile
on 10.1 and later. This change hence doesn't have any effect on
10.0 / Darwin 1.x -- except for gcc warnings about unknown flags.
The other change we discussed, ie. using -no-cpp-precomp instead
of -traditional-cpp, will break Darwin 1.x support.
2.  Why is the postgres executable added to LINK.shared?  Doesn't this
break building shlibs that are not intended to be linked into the
backend (eg libpq)?
When linking a shared object, the Darwin linker wants to know that all
the symbols are accounted for -- it doesn't allow undefined symbols.
The bundle_loader flag gives the linker a hint: these symbols will be
provided by the application that loads us, so you don't need raise
an error about them being undefined.
In other words, the -bundle_loader /path/to/postgresql doesn't affect
the build results at all. In the case where there are no undefined 
symbols
(like in libpq), the flag is ignored.

3.  What's with the WITHOUT_DARWIN_BUNDLE_LOADER hack for ecpg?
The linker doesn't like symbols that are defined both in the shared 
object
that's being linked and the bundle_loader binary. ecpg contains symbols
that are in the postgresql binary, too, leading to the link failing with
multiply-defined errors.

The easiest way for me to fix this -- I don't really know my way around 
the
source base -- was to specify by hand that the -bundle-loader flag isn't
used on these occasions. Perhaps a more prudent way would be to
examine which shared objects actually have undefined symbols and
use the bundle-loader flag only when linking them, but I think it'd lead
to much more maintenance and bloated Makefiles.

cheers
mk
Marko Karppinen <[EMAIL PROTECTED]> writes:
While we're on a Mac-related note, I managed to compile PostgreSQL on
Mac OS X 10.2 Jaguar with two-level namespace support.
 This is quite useful, so I hope you can look
into incorporating the changes.

http://www.markokarppinen.com/pg-two-level-namespace.diff


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

Re: [HACKERS] pg_dump doesn't dump binary compatible casts

[Jan Wieck]

Tom Lane wrote:

Jan Wieck <[EMAIL PROTECTED]> writes:
I classify this problem as a bug. Objections?
The question is not whether it is a bug, the question is what is correct
behavior instead.
Not yet, but when we have a decision and a fix it'll be the criterium 
for applying it to 7.4 and maybe backpatch into 7.3.


IMHO a binary compatible cast should be dumped if one or both namespaces 
of the underlying data types is included in the dump.
That would just replace one bug with another (viz, failure to restore
if one of the datatypes wasn't included in the dump).  Also, what of
user-defined casts between two system datatypes?  pg_catalog is never
considered part of the dump AFAIR.
The first idea that came to mind is to dump the cast if both underlying
types are either system types or included in the dump.  But I think that
would end up dumping built-in binary casts, which is no good either.
I hate to think of looking at the cast's OID vs. lastsysoid to decide
if it was a built-in cast, but maybe there's no other way.
User defined casts between builtin types are a bad thing anyway. They 
are only good for special backward compatibility cases or to support 
applications that are broken by design anyway. And a user defined binary 
cast between builtin types is just the worst case. Thus, braking the 
application with every dump+restore to remind the programmer that 
there's still something he has to fix is the _right thing_.

CREATE CAST for that beast should spit out a popup asking

"Do you really ..."

with "No" being the default! Well, a WARNING might do for now.

That eliminated, a cast should be dumped if one or more of the three 
objects (source, target and function) are not builtin AND all the 
non-builtin objects belong to namespaces included in the dump.

Jan

--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [HACKERS] pg_dump doesn't dump binary compatible casts

[Tom Lane]

Jan Wieck <[EMAIL PROTECTED]> writes:
> That eliminated, a cast should be dumped if one or more of the three 
> objects (source, target and function) are not builtin AND all the 
> non-builtin objects belong to namespaces included in the dump.

Where "builtin" is defined as "belongs to pg_catalog schema", you mean?
I think that works for me ...

regards, tom lane

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] [GENERAL] Can't Build 7.3.4 on OS X

[Tom Lane]

Marko Karppinen <[EMAIL PROTECTED]> writes:
>> 3.  What's with the WITHOUT_DARWIN_BUNDLE_LOADER hack for ecpg?

> The linker doesn't like symbols that are defined both in the shared 
> object
> that's being linked and the bundle_loader binary. ecpg contains symbols
> that are in the postgresql binary, too, leading to the link failing with
> multiply-defined errors.

> The easiest way for me to fix this -- I don't really know my way around 
> the
> source base -- was to specify by hand that the -bundle-loader flag isn't
> used on these occasions.

I think the right fix is that "-bundle-loader /path/to/postgres" should
be included in the link only for shlibs that are intended to be loaded
into the backend.  Otherwise you are lying to the linker; the mere fact
that it fails to fail doesn't make it the right thing to do.  (As an
example of how it could go wrong, what if there is a real undefined
symbol problem in libpq, but it happens to match some backend symbol?)

We used to have a similar requirement for some other platforms (AIX
I think), and there was a macro named something like BE_SHLIBS to
include the right platform-dependent stuff for such shlibs.  Not sure
what happened to it.  Peter, do you remember that?

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[HACKERS] website announcement for 7.4 beta

[Neil Conway]

Is there a reason why there haven't been any 7.4 beta announcements on
the main website?

-Neil



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

Re: [HACKERS] [GENERAL] Can't Build 7.3.4 on OS X

[Peter Eisentraut]

Tom Lane writes:

> We used to have a similar requirement for some other platforms (AIX
> I think), and there was a macro named something like BE_SHLIBS to
> include the right platform-dependent stuff for such shlibs.  Not sure
> what happened to it.  Peter, do you remember that?

BE_DLLLIBS; see Makefile.cygwin for example.  (AIX has a similar
requirement, but handles it differently for bizarre reasons.)

Personally, I think the two-level namespace feature is the opposite of
useful and we should stick with -flat_namespace, but I might have to give
in in the interest of having PostgreSQL behave like other packages on that
system.

-- 
Peter Eisentraut   [EMAIL PROTECTED]


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

Re: [HACKERS] website announcement for 7.4 beta

[Robert Treat]

Cause no one thought of it?  I've gone ahead and submitted something, in
the future don't hesitate to submit news items you think are of
importance to the community.

Robert Treat

On Tue, 2003-09-23 at 14:11, Neil Conway wrote:
> Is there a reason why there haven't been any 7.4 beta announcements on
> the main website?
> 
> -Neil
> 
> 
> 
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings

-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


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

[HACKERS] New SQL standard ...

[Marc G. Fournier]

Looking at the web site:

SQL:2003 Documents New!!!
This set of files represents an almost indistinuishable delta on the
actual SQL 2003 standard. In a few months, the INCITS website will post
the SQL 2003 standard for purchase

is this the same as what we talk about as SQL3??


-- Forwarded message --
Date: Tue, 23 Sep 2003 14:43:59 -0500
From: Kenneth Andresen <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Subject: New SQL standard

Hello Marc,

 It is a new SQL standard soon to be released, and it seems to be an
 important standard this time, unlike those from 95 and 99. Knowing
 PostgreSQL is the database best conforming to the SQL standards, I
 assume you already are aware of this new ISO/ANSI SQL standard,
 anyway, you might be interested in downloading the latest draft from
 http://www.wiscorp.com/SQLStandards.html.

 If you already had the draft, please ignore my e-mail.

-- 
Best regards,
 Kenneth  mailto:[EMAIL PROTECTED]


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] New SQL standard ...

[Tom Lane]

"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> SQL:2003 Documents New!!!
> This set of files represents an almost indistinuishable delta on the
> actual SQL 2003 standard. In a few months, the INCITS website will post
> the SQL 2003 standard for purchase

> is this the same as what we talk about as SQL3??

No, I think people have been calling it SQL 200X.  SQL3 was a precursor
to the SQL99 spec, IIRC.

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

[HACKERS] ecpg build on AIX 4.2.1

[Samuel A Horwitz]

I am getting Undefined symbols in build ecpg


gmake -C compatlib all
gmake[4]: Entering directory `/usr/local/postgres/pgsql/src/interfaces/ecpg/compatlib'
../../../../src/backend/port/aix/mkldexport.sh libecpg_compat.a > libecpg_compat.exp
gcc -O2 -pipe -Wall -Wmissing-prototypes -Wmissing-declarations  -Wl,-bnoentry 
-Wl,-H512 -Wl,-bM:SRE -o libecpg_compat.so libecpg_compat.a -L../../../../src/port 
-L/usr/local/ssl/lib -lm -lssl -lcrypto  -L/usr/local/ssl/lib  -L../../../../src/port 
-L/usr/local/ssl/lib -L/usr/local/ssl/lib -L../pgtypeslib -lpgtypes -L../ecpglib 
-lecpg   -Wl,-bI:../../../../src/backend/postgres.imp -Wl,-bE:libecpg_compat.exp
ld: 0711-224 WARNING: Duplicate symbol: .ParseDateTime
ld: 0711-224 WARNING: Duplicate symbol: .DecodeUnits
ld: 0711-224 WARNING: Duplicate symbol: .date2j
ld: 0711-224 WARNING: Duplicate symbol: .j2date
ld: 0711-224 WARNING: Duplicate symbol: .j2day
ld: 0711-224 WARNING: Duplicate symbol: .EncodeDateOnly
ld: 0711-224 WARNING: Duplicate symbol: .EncodeDateTime
ld: 0711-224 WARNING: Duplicate symbol: .GetEpochTime
ld: 0711-224 WARNING: Duplicate symbol: .GetCurrentDateTime
ld: 0711-224 WARNING: Duplicate symbol: .DecodeDateTime
ld: 0711-224 WARNING: Duplicate symbol: months
ld: 0711-224 WARNING: Duplicate symbol: days
ld: 0711-224 WARNING: Duplicate symbol: .tm2timestamp
ld: 0711-224 WARNING: Duplicate symbol: .DecodeInterval
ld: 0711-224 WARNING: Duplicate symbol: .EncodeInterval
ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information.
ld: 0711-317 ERROR: Undefined symbol: .PQfinish
ld: 0711-317 ERROR: Undefined symbol: .PQexec
ld: 0711-317 ERROR: Undefined symbol: .PQclear
ld: 0711-317 ERROR: Undefined symbol: .PQresultErrorField
ld: 0711-317 ERROR: Undefined symbol: .PQsetdbLogin
ld: 0711-317 ERROR: Undefined symbol: .PQstatus
ld: 0711-317 ERROR: Undefined symbol: .PQsetNoticeReceiver
ld: 0711-317 ERROR: Undefined symbol: .PQerrorMessage
ld: 0711-317 ERROR: Undefined symbol: .last_path_separator
collect2: ld returned 8 exit status
gmake[4]: *** [libecpg_compat.so] Error 1
gmake[4]: Leaving directory `/usr/local/postgres/pgsql/src/interfaces/ecpg/compatlib'


[EMAIL PROTECTED] (Samuel A Horwitz)



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

[HACKERS] invalid tid errors in latest 7.3.4 stable.

[Wade Klaver]

Hello folks,

Stumbled across an odd problem while cleaning data out of a database.  I am 
getting these "invalid tid" errors.  I tried the upgrade from 7.3.2 to 7.3.4.  
I tried a dumpall/initdb/restore... nadda.  Nothing really usefull is coming 
from the logs either, even though logging is cranked up.  If anyone can 
suggest a method to track down the cause of the following dialog with the db, 
I would greatly appreciate it.  If you need any more info, please just ask.
Thank you in advance.
 -Wade


   version   
-
 PostgreSQL 7.3.4 on i386-unknown-freebsd4.6, compiled by GCC 2.95.4
(-STABLE cvs from today)

dropsites=# begin;
BEGIN
dropsites=# delete from te_users where id = 954;
WARNING:  Error occurred while executing PL/pgSQL function c_delete_categories
WARNING:  line 14 at SQL statement
ERROR:  heap_mark4update: (am)invalid tid
dropsites=# rollback;  
ROLLBACK

Table "public.te_users"
  Column   |Type |  
Modifiers  
---+-+-
 id| integer | not null default 
nextval('"te_users_id_seq"'::text)
 username  | text| not null
 password  | text| 
 reseller  | integer | not null default 0
 directory | text| 
 contact   | integer | 
 creation_date | timestamp with time zone| default now()
 active| boolean | not null default 'f'
 domain| integer | not null default 0
 has_domain| boolean | not null default 'f'
 tutorial_type | integer | default -1
 tutorial_step | integer | default -1
 license_agreement | boolean | default 'f'
 use_header| integer | default 0
 promo | boolean | not null default 'f'
 last_billed   | timestamp without time zone | default now()
Indexes: primary_fk primary key btree (username, "domain"),
 te_users_id_key unique btree (id),
 te_users_username_lower_idx btree (lower(username))

dropsites=# \d c_categories 
 Table "public.c_categories"
   Column|  Type   |  Modifiers   
-+-+--
 id  | integer | not null default 
nextval('public.c_categories_id_seq'::text)
 category| integer | not null default 0
 userid  | integer | not null
 form| integer | not null
 name| text| 
 description | text| 
 lft | integer | 
 rgt | integer | 
 level   | integer | 
 parentid| integer | 
Indexes: c_categories_id_key unique btree (id)
Foreign Key constraints: $1 FOREIGN KEY (userid) REFERENCES te_users(id) ON 
UPDATE NO ACTION ON DELETE CASCADE,
 $2 FOREIGN KEY (form) REFERENCES c_forms(id) ON 
UPDATE NO ACTION ON DELETE CASCADE,
 c_categories_fk FOREIGN KEY (parentid) REFERENCES 
c_categories(id) ON UPDATE NO ACTION ON DELETE SET DEFAULT,
 c_categories_cat_fk FOREIGN KEY (category) REFERENCES 
c_categories(id) ON UPDATE NO ACTION ON DELETE NO ACTION



--- Source of c_delete_categories ---
CREATE OR REPLACE FUNCTION c_delete_categories() returns TRIGGER AS '

  begin
IF c_category_mutex() THEN
  -- delete entry
  DELETE FROM c_categories WHERE ID = old.id;
  
  IF (old.rgt - old.lft) > 1 THEN
-- update children
UPDATE c_categories SET ParentID = old.parentid WHERE ParentID = 
old.id;
UPDATE c_categories SET lft = lft - 1, rgt = rgt - 1, level = level - 
1 WHERE lf
t > old.lft AND lft < old.rgt;
  END IF;

  -- remove extra space
  UPDATE c_categories SET lft = lft - 2 WHERE lft > old.rgt;
  UPDATE c_categories SET rgt = rgt - 2 WHERE rgt > old.rgt;
  PERFORM c_category_clear_mutex();
  return NULL;
else
  return old;
END IF;
  end;
' language 'plpgsql';

--- source of c_category_mutex ---
CREATE OR REPLACE FUNCTION c_category_mutex() returns BOOL AS '
  DECLARE
mutex_count integer;
  BEGIN 

SELECT INTO mutex_count COUNT(*) FROM pg_class c, pg_attribute a
  WHERE a.attrelid = c.oid
AND c.relname = ''___c_category_mutex___''
AND a.attname = ''___c_category_mutex___''
AND pg_catalog.pg_table_is_visible ( c.oid );
   
IF mutex_count > 0 THEN
  RETURN ''f'';
ELSE
  CREATE TEMP TABLE ___c_category_mutex___ (___c_category_mutex___ INT2);
   

[HACKERS] bug in vacuumlo?

[Irina Sourikova]

Hi,

I tried to use vacuumlo of posgres-7.3.4/contrib/vacuumlo  and it didn't 
work
for me until I added one line:

strcat(buf, "  AND c.relname <> 'vacuum_l'");

after  
   strcat(buf, "SELECT c.relname, a.attname ");
   strcat(buf, "FROM pg_class c, pg_attribute a, pg_type t ");
   strcat(buf, "WHERE a.attnum > 0 ");
   strcat(buf, "  AND a.attrelid = c.oid ");
   strcat(buf, "  AND a.atttypid = t.oid ");
   strcat(buf, "  AND t.typname in ('oid', 'lo') ");
   strcat(buf, "  AND c.relkind = 'r'");

Is it a bug or I'm missing something?

Thank you,
Irina
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] Building from CVS issues

[jasongodden]

My bad - I didn't even notice the bison version message.
Please ignore this.

Rgds,

Jason

[EMAIL PROTECTED] wrote:

> Hi All,
> 
> I'm attempting to build from CVS so I can get into a bit of PG dev work.
> Are there any special tricks with the CVS build or is this a common error?
> 
> bison -y -d  preproc.y
> preproc.y:5276: warning: previous rule lacks an ending `;'
> preproc.y:6294: fatal error: maximum table size (32767) exceeded
> make[4]: *** [preproc.h] Error 1
> make[4]: Leaving directory
> `/home/jason/pgsql/pgsql/src/interfaces/ecpg/preproc'
> make[3]: *** [all] Error 2
> make[3]: Leaving directory `/home/jason/pgsql/pgsql/src/interfaces/ecpg'
> make[2]: *** [all] Error 2
> make[2]: Leaving directory `/home/jason/pgsql/pgsql/src/interfaces'
> make[1]: *** [all] Error 2
> make[1]: Leaving directory `/home/jason/pgsql/pgsql/src'
> make: *** [all] Error 2


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

[HACKERS] Building from CVS issues

[jasongodden]

Hi All,

I'm attempting to build from CVS so I can get into a bit of PG dev work. 
Are there any special tricks with the CVS build or is this a common error?

bison -y -d  preproc.y
preproc.y:5276: warning: previous rule lacks an ending `;'
preproc.y:6294: fatal error: maximum table size (32767) exceeded
make[4]: *** [preproc.h] Error 1
make[4]: Leaving directory
`/home/jason/pgsql/pgsql/src/interfaces/ecpg/preproc'
make[3]: *** [all] Error 2
make[3]: Leaving directory `/home/jason/pgsql/pgsql/src/interfaces/ecpg'
make[2]: *** [all] Error 2
make[2]: Leaving directory `/home/jason/pgsql/pgsql/src/interfaces'
make[1]: *** [all] Error 2
make[1]: Leaving directory `/home/jason/pgsql/pgsql/src'
make: *** [all] Error 2



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] pg_dump doesn't dump binary compatible casts

[Greg Stark]

Jan Wieck <[EMAIL PROTECTED]> writes:

> braking the application with every dump+restore to remind the programmer
> that there's still something he has to fix is the _right thing_.

I would hate to be the sysadmin restoring a database after a hardware failure
scratching my head to try to figure out why the restore isn't working.

[But then I'm not a fan of treating pg_dump files as if they were backups.]

-- 
greg


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[HACKERS] A multi CUP performance problem

[Li YueXin]

   I found  postgresql running on the multi CPU machine doesn't have better 
performance than single CPU machine. Can you explain the reason? And how to resolve 
the problem?

   In the Todo list, I read the below message:

 Add code to detect an SMP machine and handle spinlocks accordingly
 from distributted.net, http://www1.distributed.net/source, in
 client/common/cpucheck.cpp 

   I want to know how to handle spinlocks even if it can detect an SMP machine.

   Thank you!

---(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: [HACKERS] A multi CUP performance problem

[Christopher Browne]

Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Li YueXin) would write:
> I found postgresql running on the multi CPU machine doesn't have
> better performance than single CPU machine. Can you explain the
> reason? And how to resolve the problem?

The classic reason for this is that if your system is primarily I/O
bound, adding extra CPUs is likely to have a relatively negligible
effect on system performance.

The far-and-away most enormous increase in transactions-per-second
that people seem to see from hardware changes come from putting in a
SCSI controller with large amounts of battery-backed cache.
Additional disk drives tends to be #2.  More memory also helps.

The "problem" might very well be your expectation that adding CPUs
would necessarily have much effect on performance.
-- 
output = ("cbbrowne" "@" "cbbrowne.com")
http://www3.sympatico.ca/cbbrowne/emacs.html
DSK: STAN.K; ML EXIT -- FILE NOT FOUND

---(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: [HACKERS] A multi CUP performance problem

[Neil Conway]

On Tue, 2003-09-23 at 22:45, Li YueXin wrote:
> I found  postgresql running on the multi CPU machine doesn't have
> better performance than single CPU machine. Can you explain the
> reason?

In addition to what Christopher Browne suggests, keep in mind that
PostgreSQL spawns a separate Unix process for each client connection.
Since each process can run on at most one CPU at any given time, if the
# of concurrent queries your application submits is < than the # of CPUs
in the machine, PostgreSQL won't take advantage of all your CPUs.

It would be possible to spread the execution of a single query over
multiple CPUs (say, have different nodes in the query tree execute on
different CPUs concurrently), but I highly doubt PostgreSQL will bother
doing that anytime soon.

-Neil



---(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: [HACKERS] Development Partner

[Bruce Momjian]

xinsong wu wrote:
> Hi,
> I am a Ph.D candidate of Institute of Software, the Chinese Academic of
> Science, and the major is computer software and theory. My research
> interests are mainly focused on database management or other system
> software.I want to be a development partner of your project.Would you
> like introduce me how to participate your project?

Sure, see the developer's page and read the developer's FAQ:

http://developer.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 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] invalid tid errors in latest 7.3.4 stable.

[Tom Lane]

Wade Klaver <[EMAIL PROTECTED]> writes:
> Stumbled across an odd problem while cleaning data out of a database.  I am 
> getting these "invalid tid" errors.  I tried the upgrade from 7.3.2 to
> 7.3.4.

Hm.  We fixed something with a similar symptom as of 7.3.3:
http://archives.postgresql.org/pgsql-hackers/2003-03/msg01099.php
If you are still seeing it in 7.3.4 then maybe there's another related
problem.  Could you work up a self-contained test case?

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] A multi CUP performance problem

[Tom Lane]

Li YueXin <[EMAIL PROTECTED]> writes:
>I found  postgresql running on the multi CPU machine doesn't have
>better performance than single CPU machine. Can you explain the
>reason?

That's a rather sweeping statement to make without offering any
evidence, I should think.

If you gave details --- "I did x,y,z, on thus-and-such a platform,
and couldn't see any speedup" --- then you might get useful responses.

regards, tom lane

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

Re: [HACKERS] A multi CUP performance problem

[Hu Tian-Lei]

Not exactly, I think.

It said that postgresql "Add code to detect an SMP machine and handle 
spinlocks accordingly", so it is an issue of spinlock handling, not the distribution 
of execution tree.

In SMP enviorment, when using spinlock, when we fail to get the spin lock at a 
time, we may do a number of null loops to try to see if another threads on another cpu 
release the spin lock. It is just like M$'s EnterCriticalSection system call's last 
parameter.

Am I right?

=== 2003-09-24 00:09:00 You Wrote: ===

>On Tue, 2003-09-23 at 22:45, Li YueXin wrote:
>> I found  postgresql running on the multi CPU machine doesn't have
>> better performance than single CPU machine. Can you explain the
>> reason?
>
>In addition to what Christopher Browne suggests, keep in mind that
>PostgreSQL spawns a separate Unix process for each client connection.
>Since each process can run on at most one CPU at any given time, if the
># of concurrent queries your application submits is < than the # of CPUs
>in the machine, PostgreSQL won't take advantage of all your CPUs.
>
>It would be possible to spread the execution of a single query over
>multiple CPUs (say, have different nodes in the query tree execute on
>different CPUs concurrently), but I highly doubt PostgreSQL will bother
>doing that anytime soon.
>
>-Neil
>
>
>
>---(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

= = = = = = = = = = = = = = = = = = = =


Yours Sincerely,

Hu Tian-Lei
[EMAIL PROTECTED]
　　2003-09-24




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

   http://www.postgresql.org/docs/faqs/FAQ.html