Re: [GENERAL] Is it possible to findout actual owner of table?

dipti shah wrote: What is middleware? An application server that does all the business logic. your user software calls the application server to do things, and it in turn accesses the database.User written software is not alloweed to directly connecct to the databases at all. Classic

Re: [GENERAL] Is it possible to findout actual owner of table?

What is middleware? Is it similar to stored procedure? That what I have been doing. I have revoked all permissions from mydb schema from public and have SECURITY DEFINER enable for stored procedure to allow creating/droping/altering tables. Thanks. On Tue, Mar 9, 2010 at 12:19 PM, John R Pierce

Re: [GENERAL] kernel version impact on PostgreSQL performance

Albe Laurenz wrote: "the PostgreSQL performance atop the EXT3 file-system has fallen off a cliff" really a pretty stupid statement from them, as the performance went right back to where it was a few versions earlier. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

Re: [GENERAL] kernel version impact on PostgreSQL performance

Albe Laurenz wrote: Maybe that question is dumb, but why should a change in ext4 have an impact on a figure that was generated with ext3? To quote the link: "the PostgreSQL performance atop the EXT3 file-system has fallen off a cliff" What I'm guessing is that after finding the root problem

Re: [GENERAL] [NOVICE] How to read oracle table's data from postgre

Scott Marlowe wrote: Anyone know if this is still active / developed? http://wiki.postgresql.org/wiki/SqlMedConnectionManager That's still the right long-term approach for a lot of these problems, so while activity on it happens in bursts I don't think it will ever go completely inactive.

Re: [GENERAL] Is it possible to findout actual owner of table?

dipti shah wrote: I don't want users to create/drop/alter anything directly. They have to use stored procedure for everything. The stored procedure creates logging tables and stores many other auditing information so it is madatory to prevent users from running any direct commands. may be y

Re: [GENERAL] Is it possible to findout actual owner of table?

I don't want users to create/drop/alter anything directly. They have to use stored procedure for everything. The stored procedure creates logging tables and stores many other auditing information so it is madatory to prevent users from running any direct commands. Thanks, Dipti On Tue, Mar 9, 201

[GENERAL] How to save existing permissions on schema and later on restore it?

Hi, Could anyone please suggest me how to save the existing permissions on schema and later on restore it? Basically, by default I have revoked all the permissions from the mydb schema but there are some users who has all permissions on mydb schema. I have one stored procedure which temporary gran

Re: [GENERAL] Is it possible to findout actual owner of table?

dipti shah writes: > I have created the stored procedure that allows particular users to create > the table. I want to prevent users to drop the tables owned by someone esle > and hence, I am making owner of each table created by this stored procedure > to super user(postgres) so that no one will

[GENERAL] Is it possible to findout actual owner of table?

Hi, I have created the stored procedure that allows particular users to create the table. I want to prevent users to drop the tables owned by someone esle and hence, I am making owner of each table created by this stored procedure to super user(postgres) so that no one will be allowed to drop/alte

Re: [GENERAL] managing tablespaces like files?

I would like to thank both John and Scott for the help. It is very clear to me that PostgreSQL isn't the ideal solution for my current model. The conversation has gotten me thinking of ways the model could be modified to work with PostgrSQL (and other client/server RDBM). Thus I will return to t

Re: [GENERAL] autovacuum question

"Scot Kreienkamp" writes: >> Why not just add an 'analyze' as the last step of the restore job? > Due to the amount of time it takes. The disks are slow enough to make a > database-wide analyze painful since I would have to repeat it every 1-2 > hours, IE every reload time. You claimed that b

Re: [GENERAL] autovacuum question

On Mon, Mar 8, 2010 at 5:13 PM, Scot Kreienkamp wrote: Hi everyone, I have a database that is constantly getting reloaded several times per day from production backups and is used for reporting purposes. The problem I'm having with it is that the database seems to be much slower than the oth

Re: [GENERAL] autovacuum question

On Mon, Mar 8, 2010 at 5:13 PM, Scot Kreienkamp wrote: > Hi everyone, > > I have a database that is constantly getting reloaded several times per > day from production backups and is used for reporting purposes. The > problem I'm having with it is that the database seems to be much slower > than

Re: [GENERAL] managing tablespaces like files?

On Mon, Mar 8, 2010 at 4:58 PM, Sam Carleton wrote: > On Mon, Mar 8, 2010 at 4:07 PM, John R Pierce wrote: > >> >> and what happens if someone copies your directory without shutting down >> the instance? >> > > Well, that is an issue right now, the current SQLite DB is locked by the > app and can

Re: [GENERAL] managing tablespaces like files?

Sam Carleton writes: > One of the cases I need to be concerned with is the case where the > application is shut down, the user goes in and deletes the tablespace folder > by way of deleting the parent folder. How will PostgreSQL handle such a > situation? It won't like it. If your users are like

Re: [GENERAL] managing tablespaces like files?

On Mon, Mar 8, 2010 at 5:46 PM, John R Pierce wrote: > >> I hear you, but I am not willing to throw in the towel, just yet... >> Generally speaking, is there a lot of metadata that would need to be >> exported? As I think about this, I am thinking I would have to read in ALL >> the system table

Re: [GENERAL] managing tablespaces like files?

Sam Carleton wrote: On Mon, Mar 8, 2010 at 4:07 PM, John R Pierce > wrote: and what happens if someone copies your directory without shutting down the instance? Well, that is an issue right now, the current SQLite DB is locked by the app and cannot current

[GENERAL] autovacuum question

Hi everyone, I have a database that is constantly getting reloaded several times per day from production backups and is used for reporting purposes. The problem I'm having with it is that the database seems to be much slower than the others I have that are more static. I suspect that is due to t

Re: [GENERAL] managing tablespaces like files?

On Mon, Mar 8, 2010 at 4:07 PM, John R Pierce wrote: > > and what happens if someone copies your directory without shutting down the > instance? > Well, that is an issue right now, the current SQLite DB is locked by the app and cannot currently be copied while the app is running. Or at least I

Re: [GENERAL] managing tablespaces like files?

On Mon, Mar 8, 2010 at 3:52 PM, John R Pierce wrote: > > I don't think PostgreSQL is going to work for you if thats a requirement. > A tablespace doesn't contain the metadata for the items in it, thats stored > in the main cluster space (pg_catalog, etc) > Would it be difficult to export the me

Re: [GENERAL] DROP column: documentation unclear

On Mon, Mar 08, 2010 at 05:09:14PM +0100, Adrian von Bidder wrote: > Hi, > > The documentation about DROP COLUMN is a bit unclear: > > | The DROP COLUMN form does not physically remove the column, but simply > | makes it invisible to SQL operations. Subsequent insert and update > | operations in

Re: [GENERAL] managing tablespaces like files?

Sam Carleton wrote: On Mon, Mar 8, 2010 at 3:52 PM, John R Pierce > wrote: I don't think PostgreSQL is going to work for you if thats a requirement. A tablespace doesn't contain the metadata for the items in it, thats stored in the main cluster space

Re: [GENERAL] managing tablespaces like files?

Sam Carleton wrote: The server of my client/server program is currently using SQLite, an embedded file based SQL engine. I am looking for a client/server based RDBM and have narrowed it down to either PosgreSQL or Firebird. I would prefer to use PosgreSQL, but the management of the physical

[GENERAL] managing tablespaces like files?

The server of my client/server program is currently using SQLite, an embedded file based SQL engine. I am looking for a client/server based RDBM and have narrowed it down to either PosgreSQL or Firebird. I would prefer to use PosgreSQL, but the management of the physical files are concerning me.

[GENERAL] DROP column: documentation unclear

Hi, The documentation about DROP COLUMN is a bit unclear: | The DROP COLUMN form does not physically remove the column, but simply | makes it invisible to SQL operations. Subsequent insert and update | operations in the table will store a null value for the column. Thus, | dropping a column is qu

Re: [GENERAL] Enterprise DB's windows package and upgrading windows.

On Mon, Mar 8, 2010 at 7:35 PM, John Moran wrote: > Hello, > > I'd like to upgrade my windows box from windows vista to windows 7. It > runs the enterprise DB postgres package, as well as being the master > of a Slony-I cluster. Is this likely to work without incident? Is > there anything I should

[GENERAL] Enterprise DB's windows package and upgrading windows.

Hello, I'd like to upgrade my windows box from windows vista to windows 7. It runs the enterprise DB postgres package, as well as being the master of a Slony-I cluster. Is this likely to work without incident? Is there anything I should be aware of? Regards, John Moran -- Sent via pgsql-general

Re: [GENERAL] 8.3.10 Changes

Brad Nicholson writes: > Could someone please point me towards the changes for 8.3.10 that was > mentioned on -announce this morning? The release notes haven't been prepared yet. You can browse the CVS commit logs if you want the raw data. > Also, any idea when this is going to be released? Mo

[GENERAL] ERROR: Package compat-postgresql-libs-4-1PGDG.rhel4.i386.rpm is not signed

Hi, I have an error while installing Postsgresql 8.3 in Cent OS 4. I did the following... # rpm -ivh pgdg-centos-8.3-6.noarch.rpm # #vi /etc/yum.repos.d/CentOS-Base.repo Add "exclude=postgresql*" to both section [base] and [update]. #yum install postgresql-server Dependencies Resolved =

[GENERAL] 2 questions to ask

Hi, I'm using vs2005 to debug PG, and I have 2 questions to ask: 1. I want to add a function to contrib/xml2/xpath.c I changed xpath.c, pgxml.sql, pgxml.sql.in at the same time, then I builded and installed as the document said.When I execute "psql test < pgxml.sql", all functions created

[GENERAL] 8.3.10 Changes

Could someone please point me towards the changes for 8.3.10 that was mentioned on -announce this morning? Also, any idea when this is going to be released? -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. pgsql-general -- Sent via pgsql-general mailing list (pgsq

[GENERAL] Entering a character code in a query

I would like to use the following query: SELECT english || '\n' || english || '\x2028' || french AS output FROM vocab_words_translated; where \x2028 is the hexadecimal code for a soft carriage return. However, this does not work. Can anyone help with this problem? Thanking you, John Pert

Re: [GENERAL] obsessive-compulsive vacuum behavior

Ben Chobot writes: > ...and so on. It's been running for an hour or so now, when it appears it > shouldn't take 10 minutes. This seems pretty weird to me has anybody else > seen this behavior? I'm not even sure what details I could report which would > help figure out what's going on. You'

[GENERAL] PostgreSQL RPM sets for 9.0 Alpha4 released

PostgreSQL RPM Building Project released RPM sets for 4th Alpha of the upcoming 9.0 release. Please note that these packages are **not** production ready. They are built for Fedora 7,8,11,12 and RHEL/CentOS 4,5. These packages *do* require a dump/reload, even from the third alpha packages, bec

Re: [GENERAL] Libpq: copy file to bytea column

> What I can't understand is why PQputCopyData() encodes incoming character > string, which was exported by "COPY ... TO '/tmp/t'" SQL command under psql > prompt, for bytea columns and refuse to accept or misinterpret zero value. As far as PQputCopyData() is concerned, server and/or client does

Re: [GENERAL] Libpq: copy file to bytea column

> > The data types of tableout.c1 and tablein.c1 are both bytea. > > I first export tableout.c1 to a file: > > > > db1=# COPY (SELECT c1 FROM tableout LIMIT 1) TO '/tmp/t'; > > > > Then I try to import the file to another table. > > > > This works without flaw: > > > > db1=# COPY tablein FROM

Re: [GENERAL] XML Index again

Yup you are right however I am trying to run benchmarks with the two solutions. The xml solution will give us more flexibility in the future , just in case we do not have attribute/value lists :) On Mon, Mar 8, 2010 at 1:33 PM, Alban Hertroys < dal...@solfertje.student.utwente.nl> wrote: >

Re: [GENERAL] ERROR: invalid byte sequence for encoding "UTF8": 0x93 Error

On Mon, Mar 8, 2010 at 9:17 AM, Albe Laurenz wrote: > Mary Y Wang wrote: >> I got the following error and not sure how to fix it. >> "psql:/tmp/030610dumpfile.txt:4369: ERROR:  invalid byte sequence for >> encoding "UTF8": 0x93 look here: http://www.depesz.com/index.php/2010/03/07/error-invalid-

Re: [GENERAL] XML Index again

On 8 Mar 2010, at 13:23, Chris Roffler wrote: > Alban > > thanks for your response. I understand what you are saying . > > >Your previous query wasn't about attributes in any specific position - it > >returned documents that contained >more than zero attributes matching a > >given name. Wh

Re: [GENERAL] XML Index again

Alban thanks for your response. I understand what you are saying . >Your previous query wasn't about attributes in any specific position - it returned documents that contained >more than zero attributes matching a given name. What are you trying to do this time? And that is exactly my proble

Re: [GENERAL] How to find out if row was modified by EXECUTE UPDATE ...

Hello 2010/3/8 A B : > Hello. > Doesn't this work in plpgsql functions? > > EXECUTE 'UPDATE mytable set ... ...' > IF FOUND THEN >       do stuff > END IF; > it doesn't work - you have to use GET DIAGNOSTICS statement GET DIAGNOSTICS integer_var = ROW_COUNT; http://www.postgresql.org/docs/8.

[GENERAL] How to find out if row was modified by EXECUTE UPDATE ...

Hello. Doesn't this work in plpgsql functions? EXECUTE 'UPDATE mytable set ... ...' IF FOUND THEN do stuff END IF; It seems it always evaluate to false in the if statement, Isn't found used to see if an update has modified rows? Are there any alternatives to selecting the row and see

Re: [GENERAL] XML Index again

On 8 Mar 2010, at 11:39, Chris Roffler wrote: > Alban > > Thanks for your help, your suggestion worked. > > I need another xpath expression to find any Attribute with Name ="" > under the Attributes node. (not just if it is in a specific position) > see query below. > How do I create an

Re: [GENERAL] XML Index again

On 8 Mar 2010, at 11:39, Chris Roffler wrote: > Alban > > Thanks for your help, your suggestion worked. > > I need another xpath expression to find any Attribute with Name ="" > under the Attributes node. (not just if it is in a specific position) > see query below. Your previous query

Re: [GENERAL] XML Index again

Alban Thanks for your help, your suggestion worked. I need another xpath expression to find any Attribute with Name ="" under the Attributes node. (not just if it is in a specific position) see query below. How do I create an index for this xpath expression ? Thanks Chris SELECT * FROM

Re: [GENERAL] Failed to run initdb: 128

2010/3/8 Richard Huxton : > On 05/03/10 20:05, Niranjan Maturi (nmaturi) wrote: >> >> Hi >> >> Thanks for the detailed explanation with the locales. I am trying to get >> approvals to use a later version in 8.2. A colleague also suggested that >> I install 8.3 version and uninstall it to clean up t

Re: [GENERAL] Failed to run initdb: 128

On 05/03/10 20:05, Niranjan Maturi (nmaturi) wrote: Hi Thanks for the detailed explanation with the locales. I am trying to get approvals to use a later version in 8.2. A colleague also suggested that I install 8.3 version and uninstall it to clean up the machine. Then I can install 8.2. I will

Fwd: [GENERAL] postgresql 8.2 startup script

We solved this off-list. Begin forwarded message: > From: Aleksandar Sosic > Date: 7 March 2010 23:39:27 GMT+01:00 > To: Alban Hertroys > Subject: Re: [GENERAL] postgresql 8.2 startup script > > On Sun, Mar 7, 2010 at 11:31 PM, Alban Hertroys > wrote: >> [...] >> That seems to indicate it isn

[GENERAL] 2 questions when using vs2005 to debug PG

Hi,I'm using vs2005 to debug PG, and I have 2 questions to ask:1. I want to add a function to contrib/xml2/xpath.c I changed xpath.c, pgxml.sql, pgxml.sql.in at the same time, then I builded and installed as the document said.When I execute "psql test < pgxml.sql", all functions created s

Re: [GENERAL] Foreign key behavior different in a function and outside

On 05/03/10 18:12, Mridula Mahadevan wrote: Richard, To answer your questions, I have a live application that is running on postgresql. We are seeing this issue on certain installations and not on others. So the code is no different in each set up. I also added the trigger to table B and then the

Re: [GENERAL] FSM and VM file

On 05/03/10 15:14, akp geek wrote: Hi All - I have turned on the auto vacuum on the slave and for some reason the db is getting bloated up. The master size is only 1G and the slave is at 9.2GB now. I did cluster on couple of tables also. did any one run into this situation? Can

Re: [GENERAL] Avoiding duplicates (or at least marking them as such) in a "cumulative" transaction table.

On Mon, Mar 8, 2010 at 10:16 AM, Scott Marlowe wrote: > On Sun, Mar 7, 2010 at 11:31 PM, Allan Kamau wrote: >> On Mon, Mar 8, 2010 at 5:49 AM, Scott Marlowe >> wrote: >>> On Sun, Mar 7, 2010 at 1:45 AM, Allan Kamau wrote: Hi, I am looking for an efficient and effective solution to el

Re: [GENERAL] Transaction wraparound problem with database postgres

On Sun, Mar 7, 2010 at 6:06 PM, Markus Wollny wrote: > Hi! > > After going several months without such incidents, we now got bit by the same > problem again. We have since upgraded the hardware we ran the database > cluster on and currently use version 8.3.7. The general outline of the > proble

Re: [GENERAL] ERROR: invalid byte sequence for encoding "UTF8": 0x93 Error

Mary Y Wang wrote: > I got the following error and not sure how to fix it. > "psql:/tmp/030610dumpfile.txt:4369: ERROR: invalid byte sequence for > encoding "UTF8": 0x93 > HINT: This error can also happen if the byte sequence does > not match the encoding expected by the server, which is > con

Re: [GENERAL] Transaction wraparound problem with database postgres

Hi! > From: Scott Marlowe [mailto:scott.marl...@gmail.com] > Do your logs show any kind of error when vacuuming about > "only owner can vacuum" a table or anything? I grepped through the logs from the last four days and, no, there were none such errors whatsoever. Last vacuum analyze run retu

Re: [GENERAL] kernel version impact on PostgreSQL performance

Greg Smith wrote: > Cyril Scetbon wrote: > > Does anyone know what can be the differences between linux kernels > > 2.6.29 and 2.6.30 that can cause this big difference (TPS x 7 !) > > > http://www.phoronix.com/scan.php?page=article&item=linux_2624_2633&num=2 > > Discussed in detail at > http:/

Re: [GENERAL] Libpq: copy file to bytea column

seiliki wrote: > The data types of tableout.c1 and tablein.c1 are both bytea. > I first export tableout.c1 to a file: > > db1=# COPY (SELECT c1 FROM tableout LIMIT 1) TO '/tmp/t'; > > Then I try to import the file to another table. > > This works without flaw: > > db1=# COPY tablein FROM '/tmp

Re: [GENERAL] [NOVICE] How to read oracle table's data from postgre

2010/3/7 Devrim GÜNDÜZ : > On Mon, 2010-03-08 at 10:40 +0530, venkatra...@tcs.com wrote: >> >> I want to read oracle database table data from postgre. (like in >> oracle we  can use database links, is there something like this >> available in postgre). > > (It is not postgre, it is PostgreSQL or Po

Re: [GENERAL] obsessive-compulsive vacuum behavior

On Mon, Mar 8, 2010 at 12:26 AM, Scott Marlowe wrote: > On Sun, Mar 7, 2010 at 11:58 PM, Ben Chobot wrote: >> I've got an 8.4.2 database where it appears that vacuum keeps redoing the >> same table and indexes, never thinking it's finished: >> >> auditor=# VACUUM analyze VERBOSE repair_queue ; >

Re: [GENERAL] obsessive-compulsive vacuum behavior

On Sun, Mar 7, 2010 at 11:58 PM, Ben Chobot wrote: > I've got an 8.4.2 database where it appears that vacuum keeps redoing the > same table and indexes, never thinking it's finished: > > auditor=# VACUUM analyze VERBOSE repair_queue ; > INFO:  vacuuming "public.repair_queue" > INFO:  scanned inde