[GENERAL] Insert ImageFile in PostgreSQL
Please help me!!! Howto insert images file from Visual basic 6 into postgreSQL database server For information : my database server : Fedora Core 2 my client : Win XP Pro SP #2 with visual basic 6. thanks be4. Yahoo! Mail for Mobile Take Yahoo! Mail with you! Check email on your mobile phone.
Re: [GENERAL] Unable to Update a Record
Wang, Mary Y wrote: Richard, Thank you so MUCH. I was able to delete the record by using the OID method that you mentioned in (2). Well, if the column I mentioned in (1) is integer/bigint, then you'll want to do (3) as well and reindex. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Problem with dropping a tablespace
On Monday 01 August 2005 22:15, Michael Fuhr wrote: > On Mon, Aug 01, 2005 at 09:28:07PM +0200, Oliver Siegmar wrote: > > template1=# select * from pg_tablespace_databases(594611987); > > pg_tablespace_databases > > - > > 595675173 > > (1 row) > > > > Aha...so there seems to be a database associated to this tablespace - > > lets see which one - > > > > template1=# select * from pg_database where oid = 595675173; > > datname | datdba | encoding | datistemplate | datallowconn | > > datlastsysoid | datvacuumxid | datfrozenxid | dattablespace | datconfig | > > datacl > > -++--+---+--+ > >---+--+--+---+---+ (0 > > rows) > > > > Mhh. Nothing. > > > > How may I delete the tablespace manually? > > Perhaps a better question to ask is: why does pg_tablespace_databases() > think that database 595675173 has objects in the tablespace? What > platform are you using? If some flavor of Unix, what's the output > of the following command? > > ls -alR /path/to/tablespace/directory This directory indeed contains a subdirectory named 595675173 (the ghost's database oid ;-)) Is it save to shutdown the postmaster, remove this directory and restart the postmaster again? Are there any system tables that need updates after removing the database manually? Best Oliver ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Inheritance vs. LIKE - need advice
Dear pgsql-general moderators,
I sent this post out over the weekend.
Is there a reason why it is not getting posted to the list?
Many thanks for your assistance. I really need some advice on this
issue from people with experience using both inheritance mechanisms
in PostgreSQL.
Cheers,
Bill Bug
Hi All,
Sorry to bring up the topic of PostgreSQL inheritance again, but
after going through the archives and Google results, I still don't
have a clear sense of whether my plan to implement a schema I'm
working on is the preferred way to go.
First, I'd like to find out if the way I'm thinking about
Inheritance vs. the SQL DDL CREATE TABLE modifier LIKE is correct.
The simplest analogy I can think of from OO techniques is PGSQL
Inheritance corresponds to Class Inheritance, while LIKE is more
like an inheritable Interface (Java) or Mixin (Ruby). Inheritance
maintains strict hierarchical relationships propagating the "Class"
identity down through to all progeny. LIKE on the other hand simply
provides a means to re-use a set of fields in an unlimited number
tables without having to redefine those fields for each table you
use them in.
This view is incomplete and far from a perfect fit to the way PGSQL
Inheritance & LIKE work, but I think it's a helpful way of thinking
of these 2 related mechanisms, when trying to decide how and when
to use them in their current form. As has been mentioned many
times in posts here, as well as in the PGSQL docs, PGSQL
Inheritance is only partial. Table fields are propagated as well as
the group identity, but no other RDBMS objects created on the
parent ( INDEXES, CONSTRAINTS & SEQUENCES primarily) are
inherited. As has been endlessly stated in posts here and
elsewhere, this is a significant short-coming for the PGSQL
Inheritance mechanism which those of us desirous of using
Inheritance would love to see fixed (I understand it has been on
the TODO list for many years, as this mechanism has been in the
PGSQL code base for over 15 years).
I don't agree this makes PGSQL Inheritance unusable. There are
situations where I think it can still be useful, and I describe one
below. I'd welcome feedback on that opinion, however, as I'd hate
to have my relative ignorance doom the data schema I'm about to
fill with a few million rows of data to serious problems later.
The following is an example of using both Inheritance and LIKE in
the context described above.
CREATE TABLE curation_info (
created_by TEXTNOT NULL,
create_date TIMESTAMP WITH TIME ZONE,
modified_by TEXTNOT NULL,
mod_date TIMESTAMP WITH TIME ZONE
);
CREATE TABLE book (
id_pkSERIALPRIMARY KEY,
titleTEXTNOT NULL,
author_id_fkINTNOT NULL,
publisher_id_fkINTNOT NULL,
pub_year DATENOT NULL,
total_pages INTNOT NULL
LIKE curation_info
);
CREATE TABLE novel (
id_pkSERIALPRIMARY KEY,
genre_id_fkINTNOT NULL
) INHERITS (book);
CREATE TABLE textbook (
id_pkSERIALPRIMARY KEY,
subject_id_fkINTNOT NULL
) INHERITS (book);
CREATE TABLE publisher (
id_pkSERIALPRIMARY KEY,
nameTEXTNOT NULL,
address_id_fkINTNOT NULL,
LIKE curation_info
);
CREATE TABLE author (
id_pkSERIALPRIMARY KEY,
last_name TEXTNOT NULL,
first_name TEXTNOT NULL,
middle_name TEXTNOT NULL,
address_id_fkINTNOT NULL,
LIKE curation_info
);
This is not the best way to model book info (for instance, books
are only allowed to have 1 author in this schema), but it will help
me to make my point.
Books, novels and textbooks will be considered equivalent in the
context of many queries. At the same time, there will be other
queries where it will be important to consider novels & textbooks
as distinct entities. The PGSQL Inheritance mechanism easily
supports both of these situations.
The curation fields listed in the 'curation_info' table are found
ubiquitously in tables throughout many data schema. However, it is
not likely there would be a circumstance where you would want to
consider all tables containing these fields "curatable entities" to
be queried as a group. That simply makes no sense. In this case,
LIKE seems to be the best way to propagate these fields, since it
doesn't couple all tables containing them to the parent
'curation_info' table.
As I see it, there are at least 3 major problems with adopting such
a schema - despite the obvious efficiencies it offers (most of
which have been reported elsewhere):
1) none of the parent table ('book') CONSTRAINT
Re: [GENERAL] Check postgres compile-time options
On Mon, Aug 01, 2005 at 08:32:17PM -0400, Tom Lane wrote: > Laura Vance <[EMAIL PROTECTED]> writes: > > Now I have to upgrade to Fedora Core 4, which uses a much higher version > > of PostgreSQL. Unfortunately the Pg.pm support is gone (it's wrapped by > > DBI/DBD, which wasn't that hard to convert my apps, and they work except > > for some inconsistent errors that I'll figure out later), and libpq++.so > > is gone (which is where the hard part seems to be coming in). > > libpq++ is still around, it's just not bundled into the core Postgres > distribution anymore. Look on pgfoundry.org or gborg.postgresql.org. > I'm pretty sure you can still get Pg.pm too if you want it. Pg.pm is called "pgperl" and it's registered as a project on gborg. I'm not sure if they ever "released" any files there, but you can get the code via CVS and it works flawlessly. -- Alvaro Herrera () "La espina, desde que nace, ya pincha" (Proverbio africano) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL vs. MySQL
On Aug 1, 2005, at 4:33 PM, Robert Treat wrote: On Monday 01 August 2005 13:52, Scott Marlowe wrote: On Mon, 2005-08-01 at 11:44, [EMAIL PROTECTED] wrote: Hi all, I am sorry for a stupid easy question, but I'am PostgreSQL novice. Our development team has encountered problem with trying to install and maintain cluster (pgcluster) on our production database. So they have decided to switch the entire solution to MySql database. a) have you got any good/bad experience with administering and running cluster on PostrgeSQL DB? b) would u switch PostgreSQL to MySql (7.4 --> 4.1) and why? I would never switch from pgsql to mysql. Here's a short list of the reasons why: http://sql-info.de/mysql/gotchas.html If they're having problems and haven't asked for help in these lists, then I'm willing to be they're just looking for an excuse to change to what they're comfortable with, and not really serious about using postgresql. --snip-- Can someone point me to the multi-master replication docs for my$ql 4.1? I agree with Scott, sounds like they are looking for an excuse. I don't believe mysql 4.1 has multi-master replication. At least not open sourced. If you find them, I'd be interested in reading/learning about it. I believe Mysql 5.0 will have a multi-master storage engine. But, it will have a lot of gotchas too. For one, it's all in memory, so be prepared to have several machines with lots and lots of RAM. The Mysql docs for 5.0 cover it quite well. Watch out for its wastefulness though. It does things like reserve space based on the max size of a field, so if you have a varchar(250), then every row in the database will have 250 bytes of space allocated, even if you only use 1. At least, that's how it was documented the last time I looked at it. I had to rule out the mysql cluster for a project I'm working on due to how some things are implemented. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SELECT count(*) Generating Lots of Write Activity
"Logan Bowers" <[EMAIL PROTECTED]> writes: > I'm potentially having a strange performance problem. I have a BIG table: > ~100M, ~1KB rows. I do a SELECT count(*) from it (I know it will be slow) > and as I watch procinfo on my DB server I see a huge amount of write > activity. Thus, The only thing I can think of is that you have a lot of dirty page buffers (either in PG's shared buffer area or the kernel's page cache) due to prior write activity, and they are getting written out to make room for the sequential scan's incoming buffers. > 3) Are my tools lying to me (i.e. procinfo is wrong)? Perhaps; if you're on Linux try 'vmstat' instead of procinfo as a check. -Doug ---(end of broadcast)--- TIP 1: 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] tsearch2 and colons or urls
I'm trying to do a full text search for URL's stored in a table.
SELECT *,headline(description,q), rank(vectors,q) FROM bb_item,
to_tsquery('http://www.yahoo.com') AS q WHERE vectors @@ q ORDER BY
rank(vectors,q) DESC LIMIT 10 OFFSET 0
I get a general syntax error. I know it;s the colon in the URL causing
it, but what is the wise solution. ( Other than drop the http:// )
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] SELECT count(*) Generating Lots of Write Activity
"Logan Bowers" <[EMAIL PROTECTED]> writes: > I'm potentially having a strange performance problem. I have a BIG > table: ~100M, ~1KB rows. I do a SELECT count(*) from it (I know it will > be slow) and as I watch procinfo on my DB server I see a huge amount of > write activity. Thus, > 1) Why does this statement generate any writes at all? It could be that it's evicting unrelated dirty pages from cache (although PG 8.0 is supposed to try to avoid doing that during a simple seqscan). Another theory is that the table has a whole lot of recently-changed rows, and the writes are a side effect of the SELECT setting commit hint bits to tell future transactions what it found out about the commit status of the rows. I dunno what procinfo is --- personally I would use strace and see exactly which file(s) the database processes are issuing writes against. Also check whether a second SELECT against the same table continues to issue writes... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] could not bind IPv6 socket
[EMAIL PROTECTED] writes: > Hi, Does anyone have any experience to see the messaege below? > %pg_ctl start > postmaster starting > LOG: could not bind IPv6 socket: > HINT: Is another postmaster already running on port 5432? If not, wait a few > seconds and retry. Hmm, it's pretty odd that there's not any kernel errno message after the "socket: ", but otherwise this is not very surprising. There are a lot of platforms where libc thinks that IPv6 sockets exist but the kernel doesn't agree, and the above is the expected result in such cases. The postmaster will try to bind to the IPv6 address that getaddrinfo() told it to try to bind to, fail, and emit a bleat like the above. As long as there is an IPv4 address we can successfully bind to, no harm done. > Does anyone know the way to make the log not appear? Get your kernel and libc to agree about whether IPv6 is enabled. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] feeding big script to psql
=?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= <[EMAIL PROTECTED]> writes: > I know it would be faster with COPY, but this is extremly slow, and the > bottleneck is psql. > What is the problem? Hmm, does the Windows port have readline support, and if so does adding the "-n" switch to the psql invocation fix the problem? Or you could try feeding the script with -f switch or \i rather than "psql http://archives.postgresql.org
[GENERAL] SELECT count(*) Generating Lots of Write Activity
Hello, I’m potentially having a strange performance problem. I have a BIG table: ~100M, ~1KB rows. I do a SELECT count(*) from it (I know it will be slow) and as I watch procinfo on my DB server I see a huge amount of write activity. Thus, 1) Why does this statement generate any writes at all? 2) Is it possible to tweak something to make it avoid doing so? 3) Are my tools lying to me (i.e. procinfo is wrong)? Thanks! Oh, and our server configuration is: Postgres 8.0.1, Linux 2.6, AMD64, 4GB RAM, and 3TB of storage (two RAID5 volumes striped together) Logan Bowers
[GENERAL] could not bind IPv6 socket
Hi, Does anyone have any experience to see the messaege below? %pg_ctl start postmaster starting LOG: could not bind IPv6 socket: HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. We don't use IPv6. Also, DB seems to be alright. I hope it doesn't have any bad cause to have problems. Would it be ok to ignore the messages? Does anyone know the way to make the log not appear? OS:Solaris9 Version:8.03(64bit) Thanks in advance, Sincerely, Hisatomo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] feeding big script to psql
Hi, A generated a big SQL script (about 20 Mb), and fed it to psql. I was very surprised that within a minute psql became quite slow. There were areas, where less than 10 row were inserted in a second. This is on a WinXP machine with local server 8.0.3, and only I use it. Looking at the log files of PG the commands are executed fast. I have monitored psql with te TaskManager, and it was at 45-50% CPU (this is Hyperthreaded, so 50% is the max for one thread), and the memory allocation/deallocation was very active, even about +-2Mb/sec. The command were simple create table and insert commands chunked into several lines like this: CREATE TABLE aeloleg( vevo CHAR(6), szallito INTEGER, datum DATE, hatarido DATE, vevo_nev CHAR(30), ir_szam INTEGER, helyseg CHAR(20), cim CHAR(20), befizetes INTEGER, terheles INTEGER, hitel INTEGER, rendeles INTEGER, jel CHAR(1), trans INTEGER, szoveg TEXT, storno BOOLEAN) WITHOUT OIDS; The insert commands for one table were surrounded by BEGIN and COMMIT like this: CREATE BEGIN INSERT INSERT ... INSERT COMMIT I know it would be faster with COPY, but this is extremly slow, and the bottleneck is psql. What is the problem? Regards, Otto ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Check postgres compile-time options
Laura Vance <[EMAIL PROTECTED]> writes: > Now I have to upgrade to Fedora Core 4, which uses a much higher version > of PostgreSQL. Unfortunately the Pg.pm support is gone (it's wrapped by > DBI/DBD, which wasn't that hard to convert my apps, and they work except > for some inconsistent errors that I'll figure out later), and libpq++.so > is gone (which is where the hard part seems to be coming in). libpq++ is still around, it's just not bundled into the core Postgres distribution anymore. Look on pgfoundry.org or gborg.postgresql.org. I'm pretty sure you can still get Pg.pm too if you want it. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Check postgres compile-time options
Martín Marqués wrote: El Lun 01 Ago 2005 20:13, Laura Vance escribió: I've tried other configuration test programs, but none of them seem to connect properly. Is there some incompatibility with this version of iodbc and postgresql 7.1.2? Sorry, I happen to recall that you wanted this to upgrade the PG system. Why don't you just use pg_dumpall on the PG 7.1.2 instalation, save the output file, upgrade to a newer version (try 8.0.3), and restore the backup with psql? Much easier. I'm not worried about upgrading the database, that part will be easy as you indicated. My problem has nothing to do with upgrading the server side, it's upgrading my applications. The problem goes something like this: I installed PostgreSQL back in 1999 for a senior project I was doing in college. It came in Linux Mandrake 6.5. I wrote some wrapper modules that wrapped the Pg.pm functions with my own functions. Everything was good, and I liked the database engine. I upgraded to Mandrake 7.1 and the respective PostgreSQL database engine. Pg.pm was still there, so my apps still worked just fine. Everything was still good. I upgraded to Mandrake 8.1 and the respective PostgreSQL database engine (7.1.2-19mdk). Pg.pm was still there, and now I decided that I wanted to write some C++ applications that used PostgreSQL also, so I researched how to use libpq++.so. Everything was good. I even got a job implementing my software and writing more. From there, Mandrake 9.1 had a flaw with script execution, so I couldn't upgrade to it. Now I have to upgrade to Fedora Core 4, which uses a much higher version of PostgreSQL. Unfortunately the Pg.pm support is gone (it's wrapped by DBI/DBD, which wasn't that hard to convert my apps, and they work except for some inconsistent errors that I'll figure out later), and libpq++.so is gone (which is where the hard part seems to be coming in). So I have a choice... I can either rewrite my interface for the new proprietary objects for PostgreSQL or I can convert my apps to use the ODBC driver and not have to worry as much on future upgrades. The problem is that I got bitten in the behind by my choice to avoid ODBC and use the proprietary PostgreSQL drivers. It's unfortunate that they dropped the support, but it's their choice. The bad part is that it's the sole thing that's been keeping me from upgrading. -- Thanks, Laura Vance Systems Engineer ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL vs. MySQL
On Monday 01 August 2005 13:52, Scott Marlowe wrote:
> On Mon, 2005-08-01 at 11:44, [EMAIL PROTECTED] wrote:
> > Hi all,
> > I am sorry for a stupid easy question, but I'am PostgreSQL novice.
> > Our development team has encountered problem with trying to install and
> > maintain cluster (pgcluster) on our production database. So they have
> > decided to switch the entire solution to MySql database.
> > a) have you got any good/bad experience with administering and running
> > cluster on PostrgeSQL DB?
> > b) would u switch PostgreSQL to MySql (7.4 --> 4.1) and why?
>
> I would never switch from pgsql to mysql.
>
> Here's a short list of the reasons why:
>
> http://sql-info.de/mysql/gotchas.html
>
> If they're having problems and haven't asked for help in these lists,
> then I'm willing to be they're just looking for an excuse to change to
> what they're comfortable with, and not really serious about using
> postgresql.
>
> Also, why pgcluster? why not slony or mammoth replicator? Both of
> those are fine pieces of software that handle most replication needs.
> Combine slony with pgpool and a few scripts and you've got quite a nice
> cluster setup.
>
Can someone point me to the multi-master replication docs for my$ql 4.1? I
agree with Scott, sounds like they are looking for an excuse.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Check postgres compile-time options
El Lun 01 Ago 2005 20:13, Laura Vance escribió: > > I've tried other configuration test programs, but none of them seem to > connect properly. > > Is there some incompatibility with this version of iodbc and postgresql > 7.1.2? Sorry, I happen to recall that you wanted this to upgrade the PG system. Why don't you just use pg_dumpall on the PG 7.1.2 instalation, save the output file, upgrade to a newer version (try 8.0.3), and restore the backup with psql? Much easier. -- select 'mmarques' || '@' || 'unl.edu.ar' AS email; - Martín Marqués | Programador, DBA Centro de Telemática| Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Check postgres compile-time options
Michael Fuhr wrote: On Mon, Aug 01, 2005 at 05:18:58PM -0500, Laura Vance wrote: In trying to get the ODBC installed on my test system, I've run into the problem that I need to know if my binary distribution of PostgreSQL was configured with the --enable-odbc option, and I can't seem to find out how to do that. Are you looking for "pg_config --configure"? http://www.postgresql.org/docs/7.1/static/app-pgconfig.html I think pg_config reports the configure options that were in effect when *it* was compiled, so if pg_config is from a different build than the postmaster then the options pg_config reports might not match those that the postmaster was built with (somebody please correct me if I'm mistaken). If that isn't the right one, then I'm not sure. # pg_config --configure --disable-rpath --enable-hba --enable-locale --enable-multibyte --enable-syslog --with-CXX *--with-odbc* --with-perl --with-python --with-readline --with-tcl --with-tk --with-x --datadir=/usr/share/pgsql --docdir=/usr/share/doc --includedir=/usr/include/pgsql --mandir=/usr/share/man --prefix=/usr --sysconfdir=/etc/pgsql I put asterisks around the "--with-odbc" Here's a little background on what I've tried to get the postgres/ODBC working. I went to the postgresql web site and found the section for programming interfaces and clicked on the "odbc" link. It took me to a page at GBorg for ODBC. I clicked on the link for "HOWTOs" then on the link for PostgreSQL & ODBC on Redhat (since my current system is a Mandrake system that uses red hat rpm's). I followed the instructions and was able to compile the little test program. The problem I run into is when I run the iodbc test program it gives me the following output/errors: $ iodbctest "DSN=tpeims;UID=vancel;PWD=" iODBC Demonstration program This program shows an interactive SQL processor Driver Manager: 03.52.0205.0204 1: SQLDriverConnect = Unknown connect option (Set) (205) SQLSTATE=S1009 Have a nice day. Then when I run it interactively instead of command line, it does this: $ iodbctest iODBC Demonstration program This program shows an interactive SQL processor Driver Manager: 03.52.0205.0204 Enter ODBC connect string (? shows list): ? DSN | Driver -- tpeims | PostgreSQL driver Enter ODBC connect string (? shows list): tpeims 1: SQLDriverConnect = Unknown connect option (Set) (205) SQLSTATE=S1009 Have a nice day. I've tried other configuration test programs, but none of them seem to connect properly. Is there some incompatibility with this version of iodbc and postgresql 7.1.2? Also, if this needs to be taken to pgsql-odbc, let me know, but there doesn't seem to be much activity on that list (I'm not subscribed to it yet) -- Thanks, Laura Vance Systems Engineer ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Check postgres compile-time options
On Mon, Aug 01, 2005 at 05:18:58PM -0500, Laura Vance wrote: > In trying to get the ODBC installed on my test system, I've run > into the problem that I need to know if my binary distribution of > PostgreSQL was configured with the --enable-odbc option, and I can't > seem to find out how to do that. Are you looking for "pg_config --configure"? http://www.postgresql.org/docs/7.1/static/app-pgconfig.html I think pg_config reports the configure options that were in effect when *it* was compiled, so if pg_config is from a different build than the postmaster then the options pg_config reports might not match those that the postmaster was built with (somebody please correct me if I'm mistaken). -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pgmonitor
i want to list non-system tables with psql and get the following error. any alternatives? Thanks. EPost-# \dt ERROR: invalid byte sequence for encoding "UNICODE": 0xed my database used UNICODE as the encoding. and the OS is win2k pro wich simplified chinese as default language. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Check postgres compile-time options
Hello, I have an old binary RPM distribution of Postgres (7.1.2), and I am trying to switch from the proprietary interface to the ODBC interface so that I can upgrade my servers to a more up-to-date version. In trying to get the ODBC installed on my test system, I've run into the problem that I need to know if my binary distribution of PostgreSQL was configured with the --enable-odbc option, and I can't seem to find out how to do that. I could've sworn I'd seen in the list how to display how postgres was compiled, but I've been searching the list archives for about an hour or more, and I can't find it. I'm sure that within minutes, someone will point out the specific email that has it. The documentation also says that I could go into the source tree (src/interfaces/odbc) and type make ; make install, but I can't find that directory structure anywhere on my system. The documentation that told me about this is the programmers reference for 7.1.2 These are the postgreSQL packages that are installed, and below are the odbc packages installed. I know this is an old version, but as I said, I need to switch my software to odbc to upgrade, because just upgrading postgres breaks the software that I've written. #rpm -q -a | grep -i postgres postgresql-server-7.1.2-19mdk postgresql-libs-7.1.2-19mdk postgresql-perl-7.1.2-19mdk postgresql-devel-7.1.2-19mdk postgresql-7.1.2-19mdk postgresql-odbc-7.1.2-19mdk # rpm -q -a | grep -i odbc unixODBC-2.0.8-7mdk libiodbc-3.52.2-1 libunixODBC2-2.0.8-7mdk unixODBC-gui-gtk-2.0.8-7mdk libiodbc-admin-3.52.2-1 postgresql-odbc-7.1.2-19mdk libiodbc-devel-3.52.2-1 Thanks in advance for any help. -- Thanks, Laura Vance Systems Engineer Winfree Academy Charter Schools ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] pgmonitor
What happend with pgmonitor? -- select 'mmarques' || '@' || 'unl.edu.ar' AS email; - Martín Marqués | Programador, DBA Centro de Telemática| Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problem with dropping a tablespace
On Mon, Aug 01, 2005 at 09:28:07PM +0200, Oliver Siegmar wrote: > > template1=# select * from pg_tablespace_databases(594611987); > pg_tablespace_databases > - > 595675173 > (1 row) > > Aha...so there seems to be a database associated to this tablespace - lets > see > which one - > > template1=# select * from pg_database where oid = 595675173; > datname | datdba | encoding | datistemplate | datallowconn | datlastsysoid | > datvacuumxid | datfrozenxid | dattablespace | datconfig | datacl > -++--+---+--+---+--+--+---+---+ > (0 rows) > > Mhh. Nothing. > > How may I delete the tablespace manually? Perhaps a better question to ask is: why does pg_tablespace_databases() think that database 595675173 has objects in the tablespace? What platform are you using? If some flavor of Unix, what's the output of the following command? ls -alR /path/to/tablespace/directory (Replace the path with the tablespace's directory; you'll probably need to be the directory owner or root to run "ls" without getting a "Permission denied" error). -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Problem with dropping a tablespace
Hello, I'm unable to drop a recently created tablespace (the partition on which the tablespace relies on, run out of space - postgresql seems to have a problem, now): template1=# drop tablespace disk1; ERROR: tablespace "disk1" is not empty Huh? Okay...lets dig into... template1=# select oid from pg_tablespace where spcname = 'disk1'; oid --- 594611987 (1 row) template1=# select * from pg_tablespace_databases(594611987); pg_tablespace_databases - 595675173 (1 row) Aha...so there seems to be a database associated to this tablespace - lets see which one - template1=# select * from pg_database where oid = 595675173; datname | datdba | encoding | datistemplate | datallowconn | datlastsysoid | datvacuumxid | datfrozenxid | dattablespace | datconfig | datacl -++--+---+--+---+--+--+---+---+ (0 rows) Mhh. Nothing. How may I delete the tablespace manually? Best regards Oliver ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Unable to Update a Record
Richard, Thank you so MUCH. I was able to delete the record by using the OID method that you mentioned in (2). Thanks again. Mary Wang -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Monday, August 01, 2005 9:44 AM To: Wang, Mary Y Cc: Joshua D. Drake; pgsql-general@postgresql.org Subject: Re: [GENERAL] Unable to Update a Record Wang, Mary Y wrote: > Sorry, that I didn't explain my problem very clearly. > Anyway, here is the deal: > > I'm the admin for the database, so, I've all the privileges of > updating, deletion, and reviewing and et. > > When I tried to select based on the bemsid condition, TWO ROWS > returned: > > select * from users where bemsid=949762; > > user_id | user_name | email | user_pw | realname > 4215 | 949762| [EMAIL PROTECTED] | | Hoff, John A | > > But when I tried select user_id=4215, the result return 0 rows: > > select * from users where user_id=4215; > user_id | user_name | email | user_pw | realname | status | shell | > -+--+- > (0 rows) > > I'm really confused. I want to delete user_id=4215 because it is > causing me login errors. But I can't select, update, delete that > record. I'm not sure if that record really exist. 1. What type is "user_id"? If it's a text-type, there could be unseen spaces interfering. 2. Try selecting the OID too (SELECT oid,* FROM ...) with your first query, then use that oid in your where clause. Can you see it now? Of course, this assumes you have oids defined for this table. 3. Have you tried re-indexing the table (REINDEX TABLE users) It's possible the index has become corrupted while the data is fine. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL vs. MySQL
Am Montag, den 01.08.2005, 18:44 +0200 schrieb [EMAIL PROTECTED]: > Hi all, > I am sorry for a stupid easy question, but I'am PostgreSQL novice. > Our development team has encountered problem with trying to install and > maintain cluster (pgcluster) on our production database. So they have > decided to switch the entire solution to MySql database. > a) have you got any good/bad experience with administering and running > cluster on PostrgeSQL DB? > b) would u switch PostgreSQL to MySql (7.4 --> 4.1) and why? Usually only a few installations need a cluster solution anyway. Did you try with optimized queries and a single machine postgres? You have online backup too so you dont need a cluster for this functionality. If it turns out you have to distribute load somehow, there is slony and pgpool. Regards Tino Wildenhain ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL vs. MySQL
On Mon, 2005-08-01 at 11:44, [EMAIL PROTECTED] wrote: > Hi all, > I am sorry for a stupid easy question, but I'am PostgreSQL novice. > Our development team has encountered problem with trying to install and > maintain cluster (pgcluster) on our production database. So they have > decided to switch the entire solution to MySql database. > a) have you got any good/bad experience with administering and running > cluster on PostrgeSQL DB? > b) would u switch PostgreSQL to MySql (7.4 --> 4.1) and why? I would never switch from pgsql to mysql. Here's a short list of the reasons why: http://sql-info.de/mysql/gotchas.html If they're having problems and haven't asked for help in these lists, then I'm willing to be they're just looking for an excuse to change to what they're comfortable with, and not really serious about using postgresql. Also, why pgcluster? why not slony or mammoth replicator? Both of those are fine pieces of software that handle most replication needs. Combine slony with pgpool and a few scripts and you've got quite a nice cluster setup. Of course, setting mysql up is a bit easier. But I don't trust their replication, and searching google for mysql and clustering and problems or what not will tell you why. Computer Science ain't easy, and picking a product because it seems easier to use up front is often the worst of all decisions. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Disconnect sessions and session timeouts
Can the client application be modified? It seems like it might need to be, since if it is expecting its connection to be open whenever it's running, the server killing the connection might lead it to crash. - IanOn 8/1/05, Len Walter <[EMAIL PROTECTED]> wrote: Hi,I'm looking to solve a problem where a client application is leavingidle sessions open, eventually filling up the maximum connections andstopping anyone from connecting.It seems like there's no way to disconnect sessions after a given idle timeout. There is a statement timeout, but that won't terminate thesession.Is there any way to disconnect a given session? In oracle you'd dosomething like "alter system kill session 'sid,serial#'". I've tried parsing the output of ps -efa to find out which postgres processesbelow to clients that should be disconnected, then sending them TERMor QUIT signals, but TERM has no effect and QUIT puts the wholedatabase into recovery mode. Thanks in advance,Len--Len Walter [EMAIL PROTECTED] http://crookedtimbre.net---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL vs. MySQL
On Mon, Aug 01, 2005 at 06:44:50PM +0200, [EMAIL PROTECTED] wrote: > Hi all, > I am sorry for a stupid easy question, but I'am PostgreSQL novice. > Our development team has encountered problem with trying to install and > maintain cluster (pgcluster) on our production database. So they have > decided to switch the entire solution to MySql database. > a) have you got any good/bad experience with administering and running > cluster on PostrgeSQL DB? > b) would u switch PostgreSQL to MySql (7.4 --> 4.1) and why? For pgcluster help, your best bet is to hit either their mailling list or their forums. http://pgfoundry.org/projects/pgcluster/ has more info. As for MySQL, http://sql-info.de/mysql/gotchas.html has about 100 different reasons why you don't want to use MySQL. Some favorites: Feb. 31st is a valid date Data will be silently truncated if it overflows 1/0 = NULL count(*) is an approximation easy to configure in such a way that it's not ACID ... -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [pgsql-advocacy] New MySQL to PostgreSQL Migration Guide
So should we ask to take over the sql2pg project on pgfoundry and move the existing scripts over there, as well as the migration guide? On Sun, Jul 31, 2005 at 10:51:06PM -0700, Chris Travers wrote: > Hi; > > I have just posted a MySQL to PostgreSQL migration guide at > http://www.metatrontech.com/wpapers and it is free for pretty much any > use (I do have a somewhat toned-down advertising clause in the copyright > license). > > It is a first draft and formatting and other stuff will be likely > changed. I am also considering creating a set of wrapper functions > which will provide compatibility with many of the non-standard functions > in MySQL. > > Any feedback, etc. is appreciated. > > Best Wishes, > Chris Travers > Metatron Technology Consulting > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Unable to Update a Record
Wang, Mary Y wrote: Sorry, that I didn't explain my problem very clearly. Anyway, here is the deal: I'm the admin for the database, so, I've all the privileges of updating, deletion, and reviewing and et. When I tried to select based on the bemsid condition, TWO ROWS returned: select * from users where bemsid=949762; user_id | user_name | email | user_pw |realname 4215 | 949762| [EMAIL PROTECTED] | | Hoff, John A | But when I tried select user_id=4215, the result return 0 rows: select * from users where user_id=4215; user_id | user_name | email | user_pw | realname | status | shell | -+--+- (0 rows) I'm really confused. I want to delete user_id=4215 because it is causing me login errors. But I can't select, update, delete that record. I'm not sure if that record really exist. 1. What type is "user_id"? If it's a text-type, there could be unseen spaces interfering. 2. Try selecting the OID too (SELECT oid,* FROM ...) with your first query, then use that oid in your where clause. Can you see it now? Of course, this assumes you have oids defined for this table. 3. Have you tried re-indexing the table (REINDEX TABLE users) It's possible the index has become corrupted while the data is fine. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] PostgreSQL vs. MySQL
Hi all, I am sorry for a stupid easy question, but I'am PostgreSQL novice. Our development team has encountered problem with trying to install and maintain cluster (pgcluster) on our production database. So they have decided to switch the entire solution to MySql database. a) have you got any good/bad experience with administering and running cluster on PostrgeSQL DB? b) would u switch PostgreSQL to MySql (7.4 --> 4.1) and why? thx for any answer Vrata ---(end of broadcast)--- TIP 1: 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] Unable to Update a Record
Sorry, that I didn't explain my problem very clearly. Anyway, here is the deal: I'm the admin for the database, so, I've all the privileges of updating, deletion, and reviewing and et. When I tried to select based on the bemsid condition, TWO ROWS returned: select * from users where bemsid=949762; user_id | user_name | email | user_pw |realname | status | shell | unix_pw | unix_status | unix_uid | unix_box | add_date | confirm_hash | mail_siteupdates | mail_va | authorized_keys | email_new | people_view_skills | people_resume | timezone | language | third_party | personal_status | bemsid | sensitive_info | reason_access | organization | brass_first_time | mail_sitenews_update | doclinks_sort_order -+---+---+-+ -++---+-+-+--+-- ++--+--+-+-- ---+---++---+--+ --+-+--++--- -+---+--+--+ --+- 4215 | 949762| [EMAIL PROTECTED] | | Hoff, John A | A | /bin/bash | | N |0 | shell1 | 1114441842 | |0 | 0 | | | 0 | | GMT |1 | 1 | uscompany | 949762 || | IDS | 0|0 | 1828 | 949762| [EMAIL PROTECTED] | | Hoff, John A | A | /bin/bash | | A | 436 | shell1 | 1076368047 | |0 | 0 | | | 0 | | GMT |1 | 1 | uscompany | 949762 || | IDS | 0| | D (2 rows) But when I tried select user_id=4215, the result return 0 rows: select * from users where user_id=4215; user_id | user_name | email | user_pw | realname | status | shell | unix_pw | unix_status | unix_uid | unix_box | add_date | confirm_hash | mail_siteupdates | mail_va | authorized_keys | email_new | people_view_skills | people_resume | timezone | language | third_party | personal_status | bemsid | sensitive_info | reason_access | organization | brass_first_time | mail_sitenews_update | doclinks_sort_order -+---+---+-+--++---+ -+-+--+--+--+--+ --+-+-+---+- ---+---+--+--+-+ -+++---+--+- -+--+- (0 rows) I'm really confused. I want to delete user_id=4215 because it is causing me login errors. But I can't select, update, delete that record. I'm not sure if that record really exist. I used the Vaccum, but it didn't help. Thanks in advance. Mary Wang -Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] Sent: Friday, July 29, 2005 5:18 PM To: Wang, Mary Y Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Unable to Update a Record Wang, Mary Y wrote: > Hi, > > I'm running postgressql 7.1.3-2. > I've a > > When I did a select on the table, I was able to see that row. > However, > when I tried to update that row, I got 'Update 0', I even tried to > delete that row, I couldn't. It seems like the database is confused. I > did the Vacuum, but still didn't help. It would probably be helpful to see what query you are running, the table information... things like that. > > Any suggestions? > > Thanks > Mary Wang > > > -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: 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
Schema Pivileges was Re: [GENERAL] Alter privileges for all tables
Sean Davis wrote: > Sorry to ask this again, but I seem to have misplaced the original > discussion and can't find it in the archives--could someone point me to the > thread on functions to batch alter privileges on all tables (or a subset) in > a database? On a somewhat related note, is revoking all on a schema sufficient to prevent users (or, say, group public) from accessing anything w/i that schema, or do I need to explicitly set rights on all of the objects w/i the schema? -- Peter Fein [EMAIL PROTECTED] 773-575-0694 Basically, if you're not a utopianist, you're a schmuck. -J. Feldman ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Strange delete behaviour
On Mon, Aug 01, 2005 at 04:02:14PM +0200, Renzo Kottmann wrote: > > Do other tables have foreign key references to t_node? If so, are > > there indexes on those tables' foreign key columns? How many records > > are in t_node and any tables that reference it? Do you keep the > > tables vacuumed and analyzed? > > Yes. I vacuumed and analyezed. There are several references (t_annotation > has two references to t_node): Here is the dicription of the tables. The description for t_annotation shows the two references to t_node but no indexes on the referencing columns (ann_startnode_id and ann_endnode_id). When you delete records from t_node, the database has to check whether those deletions would cause a foreign key violation, so it has to search t_annotation for matching foreign keys. Without indexes on the referencing columns, the planner has to use a sequential scan instead of considering an index scan, so those searches are likely to be slow. Try creating indexes on the referencing columns (ann_startnode_id and ann_endnode_id) and on any other columns that refer to other tables. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Strange delete behaviour
"Renzo Kottmann" <[EMAIL PROTECTED]> writes: > "t_annotation_ann_startnode_id_fkey" FOREIGN KEY (ann_startnode_id) > REFERENCES t_node(node_global_id) MATCH FULL > "t_annotation_ann_endnode_id_fkey" FOREIGN KEY (ann_endnode_id) > REFERENCES t_node(node_global_id) MATCH FULL You need indexes on ann_startnode_id and ann_endnode_id. There might be some other missing indexes too --- check each of your foreign key constraints. Postgres doesn't force you to keep an index on the referencing side of a foreign key ... but if you want deletes from the master table to be fast, you'd better have one. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Alter privileges for all tables
Hi Sean, On Aug 1, 2005, at 8:40 AM, Sean Davis wrote: Sorry to ask this again, but I seem to have misplaced the original discussion and can't find it in the archives--could someone point me to the thread on functions to batch alter privileges on all tables (or a subset) in a database? You can find some functions for this here: http://pgedit.com/node/20 John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Strange delete behaviour
> On Mon, Aug 01, 2005 at 01:57:32PM +0200, Renzo Kottmann wrote:
>> If I try a
>>
>> delete
>> from t_node
>> where node_doc_id = XX;
>>
>> from inside a plpgsql function
>> ...
>> The deletion does not finish after several minutes and the CPU is
>> running at 100% all the time unless I stop postmaster. A select works
>> normal and gives me around 2500 rows. Does anybody has an idea why this
>> happens?
>
> What happens if you execute the delete by itself, i.e., not from
> inside a function?
The same! Before I did "delete from t_node where node_doc_id = XX;"
I did
1. "delete from t_as_annotation where asann_ann_id in (select
ann_global_id from t_annotation where ann_doc_id = XX);"
2. "delete from t_annotation where ann_doc_id = XX;"
3. "delete from t_annot_set where as_doc_id = XX;"
These are the same statements in the same order like in the function.
> What output do you get if you connect to the
> database with psql and execute "EXPLAIN ANALYZE DELETE ..."?
It also hangs up with 100% CPU load.
> Do other tables have foreign key references to t_node? If so, are
> there indexes on those tables' foreign key columns? How many records
> are in t_node and any tables that reference it? Do you keep the
> tables vacuumed and analyzed?
>
Yes. I vacuumed and analyezed. There are several references (t_annotation
has two references to t_node): Here is the dicription of the tables.
Table "public.t_node"
Column | Type | Modifiers
+-+
node_global_id | integer | not null default nextval('seq_node'::text)
node_doc_id| integer | not null
node_local_id | integer | not null
node_offset| integer | not null
Indexes:
"t_node_pkey" PRIMARY KEY, btree (node_global_id)
"xt_node_01" UNIQUE, btree (node_doc_id, node_local_id)
Foreign-key constraints:
"t_node_node_doc_id_fkey" FOREIGN KEY (node_doc_id) REFERENCES
t_document(doc_id) MATCH FULL
Table "public.t_document"
Column| Type | Modifiers
-+-+
doc_id | integer | not null default
nextval('seq_document'::text)
doc_content_id | integer |
doc_lr_id | integer | not null
doc_url | text|
doc_start | integer |
doc_end | integer |
doc_is_markup_aware | boolean | not null
Indexes:
"t_document_pkey" PRIMARY KEY, btree (doc_id)
"xt_document_01" UNIQUE, btree (doc_lr_id)
Foreign-key constraints:
"t_document_doc_content_id_fkey" FOREIGN KEY (doc_content_id)
REFERENCES t_doc_content(dc_id) MATCH FULL
"t_document_doc_lr_id_fkey" FOREIGN KEY (doc_lr_id) REFERENCES
t_lang_resource(lr_id) MATCH FULL
Table "public.t_annotation"
Column | Type |Modifiers
--+-+--
ann_global_id| integer | not null default
nextval('seq_annotation'::text)
ann_doc_id | integer |
ann_local_id | integer | not null
ann_at_id| integer | not null
ann_startnode_id | integer | not null
ann_endnode_id | integer | not null
Indexes:
"t_annotation_pkey" PRIMARY KEY, btree (ann_global_id)
"xt_annotation_01" UNIQUE, btree (ann_doc_id, ann_local_id)
Foreign-key constraints:
"t_annotation_ann_doc_id_fkey" FOREIGN KEY (ann_doc_id) REFERENCES
t_document(doc_id) MATCH FULL
"t_annotation_ann_at_id_fkey" FOREIGN KEY (ann_at_id) REFERENCES
t_annotation_type(at_id) MATCH FULL
"t_annotation_ann_startnode_id_fkey" FOREIGN KEY (ann_startnode_id)
REFERENCES t_node(node_global_id) MATCH FULL
"t_annotation_ann_endnode_id_fkey" FOREIGN KEY (ann_endnode_id)
REFERENCES t_node(node_global_id) MATCH FULL
Table "public.t_annot_set"
Column | Type |Modifiers
---++-
as_id | integer| not null default
nextval('seq_annot_set'::text)
as_name | character varying(128) |
as_doc_id | integer| not null
Indexes:
"t_annot_set_pkey" PRIMARY KEY, btree (as_id)
"xt_annot_set_01" UNIQUE, btree (as_doc_id, as_name)
Foreign-key constraints:
"t_annot_set_as_doc_id_fkey" FOREIGN KEY (as_doc_id) REFERENCES
t_document(doc_id) MATCH FULL
Table "public.t_as_annotation"
Column| Type | Modifiers
--+-+-
asann_id | integer | not null default nextval('seq_as_annotation'::text)
asann_ann_id | integer | not null
asann_as_id | integer | not null
Indexes:
"t_as_annotation_pkey" PRIMARY KEY, btree (asann_id)
"xt_as_annotation_01" btree (asann_as_id)
"xt_as
Re: [GENERAL] Strange delete behaviour
On Mon, Aug 01, 2005 at 01:57:32PM +0200, Renzo Kottmann wrote: > If I try a > > delete > from t_node > where node_doc_id = XX; > > from inside a plpgsql function > ... > The deletion does not finish after several minutes and the CPU is > running at 100% all the time unless I stop postmaster. A select works > normal and gives me around 2500 rows. Does anybody has an idea why this > happens? What happens if you execute the delete by itself, i.e., not from inside a function? What output do you get if you connect to the database with psql and execute "EXPLAIN ANALYZE DELETE ..."? Do other tables have foreign key references to t_node? If so, are there indexes on those tables' foreign key columns? How many records are in t_node and any tables that reference it? Do you keep the tables vacuumed and analyzed? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Strange delete behaviour
Post the result of
\d t_node t_documentOn 8/1/05, Renzo Kottmann <[EMAIL PROTECTED]> wrote:
Gnanavel S wrote:> post the description of the t_node and t_document tables for more> information>> On 8/1/05, Renzo Kottmann <[EMAIL PROTECTED]> wrote:>
>>Hello,I have a strange delete behaviour in my postgres 8.0.3 database:If I try adelete>>from t_node>>where node_doc_id = XX;
from inside a plpgsql functionon this table:CREATE TABLE t_node (>>node_global_id int4 DEFAULT nextval('seq_node') NOT NULL ,>>node_doc_id int4 NOT NULL ,
>>node_local_id int4 NOT NULL ,>>node_offset int4 NOT NULL ,>>FOREIGN KEY ( node_doc_id )>>REFERENCES t_document ( doc_id )>>MATCH FULL ,>>PRIMARY KEY ( node_global_id )
>>);CREATE UNIQUE INDEX idx_node ON t_node (>>node_doc_id>>node_local_id>>);The deletion does not finish after several minutes and the CPU is
>>running at 100% all the time unless I stop postmaster. A select works>>normal and gives me around 2500 rows. Does anybody has an idea why this>>happens?Thanks,>>renzo
t_node see above and in addition t_document:CREATE TABLE t_document (doc_id
int4 DEFAULT nextval('seq_document') NOT NULL ,doc_content_id int4,doc_lr_id int4 NOT NULL ,doc_urltext NULL ,doc_start int4,doc_endint4,
doc_is_markup_aware bool NOT NULL , FOREIGN KEY ( doc_content_id ) REFERENCES t_doc_content ( dc_id ) MATCH FULL , FOREIGN KEY ( doc_lr_id ) REFERENCES t_lang_resource ( lr_id )
MATCH FULL , PRIMARY KEY ( doc_id ));CREATE UNIQUE INDEX xt_document_01 on t_document (doc_lr_id);-- with regards,S.GnanavelSatyam Computer Services Ltd.
Re: [GENERAL] Strange delete behaviour
Gnanavel S wrote:
> post the description of the t_node and t_document tables for more
> information
>
> On 8/1/05, Renzo Kottmann <[EMAIL PROTECTED]> wrote:
>
>>Hello,
>>
>>I have a strange delete behaviour in my postgres 8.0.3 database:
>>
>>If I try a
>>
>>delete
>>from t_node
>>where node_doc_id = XX;
>>
>>from inside a plpgsql function
>>
>>on this table:
>>
>>CREATE TABLE t_node (
>>node_global_id int4 DEFAULT nextval('seq_node') NOT NULL ,
>>node_doc_id int4 NOT NULL ,
>>node_local_id int4 NOT NULL ,
>>node_offset int4 NOT NULL ,
>>FOREIGN KEY ( node_doc_id )
>>REFERENCES t_document ( doc_id )
>>MATCH FULL ,
>>PRIMARY KEY ( node_global_id )
>>);
>>
>>CREATE UNIQUE INDEX idx_node ON t_node (
>>node_doc_id
>>node_local_id
>>);
>>
>>The deletion does not finish after several minutes and the CPU is
>>running at 100% all the time unless I stop postmaster. A select works
>>normal and gives me around 2500 rows. Does anybody has an idea why this
>>happens?
>>
>>Thanks,
>>renzo
t_node see above and in addition t_document:
CREATE TABLE t_document (
doc_id int4 DEFAULT nextval('seq_document') NOT NULL ,
doc_content_id int4,
doc_lr_id int4 NOT NULL ,
doc_urltext NULL ,
doc_start int4,
doc_endint4,
doc_is_markup_aware bool NOT NULL ,
FOREIGN KEY ( doc_content_id )
REFERENCES t_doc_content ( dc_id )
MATCH FULL ,
FOREIGN KEY ( doc_lr_id )
REFERENCES t_lang_resource ( lr_id )
MATCH FULL ,
PRIMARY KEY ( doc_id )
);
CREATE UNIQUE INDEX xt_document_01 on t_document (doc_lr_id);
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] Alter privileges for all tables
Sorry to ask this again, but I seem to have misplaced the original discussion and can't find it in the archives--could someone point me to the thread on functions to batch alter privileges on all tables (or a subset) in a database? Thanks, Sean ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Strange delete behaviour
post the description of the t_node and t_document tables for more informationOn 8/1/05, Renzo Kottmann <[EMAIL PROTECTED]
> wrote:Hello,I have a strange delete behaviour in my postgres 8.0.3 database:
If I try adelete from t_node where node_doc_id = XX;from inside a plpgsql functionon this table:CREATE TABLE t_node (node_global_id int4 DEFAULT nextval('seq_node') NOT NULL ,
node_doc_idint4 NOT NULL ,node_local_id int4 NOT NULL ,node_offsetint4 NOT NULL , FOREIGN KEY ( node_doc_id ) REFERENCES t_document ( doc_id ) MATCH FULL ,
PRIMARY KEY ( node_global_id ));CREATE UNIQUE INDEX idx_node ON t_node (node_doc_idnode_local_id);The deletion does not finish after several minutes and the CPU is
running at 100% all the time unless I stop postmaster. A select worksnormal and gives me around 2500 rows. Does anybody has an idea why thishappens?Thanks,renzo---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings-- with regards,S.GnanavelSatyam Computer Services Ltd.
[GENERAL] Strange delete behaviour
Hello,
I have a strange delete behaviour in my postgres 8.0.3 database:
If I try a
delete
from t_node
where node_doc_id = XX;
from inside a plpgsql function
on this table:
CREATE TABLE t_node (
node_global_id int4 DEFAULT nextval('seq_node') NOT NULL ,
node_doc_idint4 NOT NULL ,
node_local_id int4 NOT NULL ,
node_offsetint4 NOT NULL ,
FOREIGN KEY ( node_doc_id )
REFERENCES t_document ( doc_id )
MATCH FULL ,
PRIMARY KEY ( node_global_id )
);
CREATE UNIQUE INDEX idx_node ON t_node (
node_doc_id
node_local_id
);
The deletion does not finish after several minutes and the CPU is
running at 100% all the time unless I stop postmaster. A select works
normal and gives me around 2500 rows. Does anybody has an idea why this
happens?
Thanks,
renzo
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Questions about Views, Rules and DBLink
Thanks for your help. But still, do you think there could be a way to alter the dblink_current_query() function so that it could return the right query? Or should I try to build the query in a function and send it through dblink instead of dblink_current_query()? I've also been told that oracle has an auditing service that records every action the users make, including the queries issued. If pg has something like that I could use it instead. On 8/1/05, Tom Lane <[EMAIL PROTECTED]> wrote: > Joao Afonso <[EMAIL PROTECTED]> writes: > > So (finally), my question is why does this happen? Using instead on > > the users_util insert rule shouldn't discard the original query and > > rewrite it according to the specified on the rule?? Is this a problem > > of dblink? > > I hadn't noticed the dblink_current_query() function before, but now > that I see it, I consider it a pretty bad idea. It certainly will not > help you the way you are hoping, because what it returns is the text of > the interactive command the backend is currently working on --- which > could be indefinitely far removed from the operation your rule is firing > for. > >regards, tom lane > ---(end of broadcast)--- TIP 1: 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
