Re: [GENERAL] invalid byte sequence

On 4/03/2011 10:18 PM, Maximilian Tyrtania wrote: Am 04.03.2011 um 11:01 schrieb Craig Ringer: On 04/03/11 00:02, Maximilian Tyrtania wrote: After upgrading to pg 9.0.3 (from 8.4.2) on my Mac OS 10.6.2 machine i find this in my log file (a lot): STATEMENT: SELECT pg_file_read('pg_log/postg

Re: [GENERAL] test data

You could try online yellow-pages and extract names from the HTML; I did this a long time ago for some reason. There may be copyright issues to consider but if you are using it for internal test data... -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general

Re: [GENERAL] database is bigger after dump/restore - why? (60 GB to 109 GB)

On Friday, March 04, 2011 5:11:04 pm Aleksey Tsalolikhin wrote: > On Fri, Mar 4, 2011 at 4:45 PM, Adrian Klaver wrote: > > On Friday, March 04, 2011 2:03:23 pm Aleksey Tsalolikhin wrote: > >> On Fri, Mar 4, 2011 at 7:53 AM, Adrian Klaver wrote: > >> >What is the data being stored in the table? >

[GENERAL] test data

I seem to like having more realistic test data, to that end I have collected a large number of first names and last names over the last few years. Now I'd kinda like to collect business names. I've been searching around and cannot find anything. I was wondering if anyone had any business na

Re: [GENERAL] database is bigger after dump/restore - why? (60 GB to 109 GB)

On Fri, Mar 4, 2011 at 4:45 PM, Adrian Klaver wrote: > On Friday, March 04, 2011 2:03:23 pm Aleksey Tsalolikhin wrote: >> On Fri, Mar 4, 2011 at 7:53 AM, Adrian Klaver >> wrote: >> >What is the data being stored in the table? >> >> For the main part, it's an XML file, we store it in the third fi

Re: [GENERAL] database is bigger after dump/restore - why? (60 GB to 109 GB)

On Friday, March 04, 2011 2:03:23 pm Aleksey Tsalolikhin wrote: > On Fri, Mar 4, 2011 at 7:53 AM, Adrian Klaver wrote: > > Weird. The pgstattuple data shows that the tables are essentially the > > same, the only difference being the dead tuples, as expected, on the > > production table. The TOAST

Re: [GENERAL] Significance of numbers in server errors?

On 4 March 2011 23:15, Sebastien Boisvert wrote: >  I know the second is the database's directory, the last is the pg_attribute > table (in this example), but I haven't figured out what the first is. I think that the first is probably the tablespace: postgres=# select oid,* from pg_tablespace; -

[GENERAL] Significance of numbers in server errors?

I'm wondering if there's a description anywhere of the significance of number reported in errors; for example I've recently run into this error: ERROR: could not read block 132 of relation 1663/16430/1249: read only 0 of 8192 bytes >From some documentation I've read (http://etutorials.org/SQL

Re: [GENERAL] updating all records of a table

robjsarg...@gmail.com (Rob Sargent) writes: > On 03/04/2011 04:54 AM, Vibhor Kumar wrote: >> >> On Mar 4, 2011, at 5:17 PM, Andrew Sullivan wrote: >> >>> On Thu, Mar 03, 2011 at 08:03:59PM -0700, Gauthier, Dave wrote: Hi: I have to update all the records of a table. I'm worried ab

Re: [GENERAL] Unprivileged access to pgsql functions?

On Fri, Mar 4, 2011 at 2:03 PM, John R Pierce wrote: > On 03/04/11 1:57 PM, Matt Warner wrote: > >> Not sure. I believe public and pg_catalog are in the path by default. Most >> of the create function declarations prepend pg_catalog, and I believe I saw >> somewhere that pg_catalog is the default

Re: [GENERAL] Unprivileged access to pgsql functions?

On 03/04/11 1:57 PM, Matt Warner wrote: Not sure. I believe public and pg_catalog are in the path by default. Most of the create function declarations prepend pg_catalog, and I believe I saw somewhere that pg_catalog is the default. But I may be misunderstanding that... CREATE FUNCTION nvl(an

Re: [GENERAL] database is bigger after dump/restore - why? (60 GB to 109 GB)

On Fri, Mar 4, 2011 at 7:53 AM, Adrian Klaver wrote: > > Weird. The pgstattuple data shows that the tables are essentially the same, > the > only difference being the dead tuples, as expected, on the production table. > The > TOAST size information shows approximately a doubling in size of the T

Re: [GENERAL] Unprivileged access to pgsql functions?

On Fri, Mar 4, 2011 at 1:56 PM, Bosco Rama wrote: > Matt Warner wrote: > > > > The function cannot be defined in the user's DB because "language C" is > > considered a security risk, so only the superuser can do that. Or that's > > what I get from reading anyway... > > psql -U postgres -d user_db

Re: [GENERAL] Unprivileged access to pgsql functions?

On Fri, Mar 4, 2011 at 1:51 PM, Andrew Sullivan wrote: > On Fri, Mar 04, 2011 at 01:41:34PM -0800, Matt Warner wrote: > > No luck: > > > > *** as postgres > > postgres=# GRANT all on function nvl(anyelement,anyelement) to public; > > GRANT > > postgres=# > > > > *** as unprivileged user > > offlo

Re: [GENERAL] Unprivileged access to pgsql functions?

Matt Warner wrote: > > The function cannot be defined in the user's DB because "language C" is > considered a security risk, so only the superuser can do that. Or that's > what I get from reading anyway... psql -U postgres -d user_db will allow the superuser to then define the function in the us

Re: [GENERAL] Unprivileged access to pgsql functions?

On Fri, Mar 4, 2011 at 1:49 PM, John R Pierce wrote: > On 03/04/11 1:41 PM, Matt Warner wrote: > >> No luck: >> >> *** as postgres >> postgres=# GRANT all on function nvl(anyelement,anyelement) to public; >> GRANT >> postgres=# >> >> *** as unprivileged user >> offload=> select nvl(0,1); >> ERROR

Re: [GENERAL] Unprivileged access to pgsql functions?

On Fri, Mar 4, 2011 at 1:48 PM, Bosco Rama wrote: > Matt Warner wrote: > > No luck: > > > > *** as postgres > > postgres=# GRANT all on function nvl(anyelement,anyelement) to public; > > GRANT > > postgres=# > > > > *** as unprivileged user > > offload=> select nvl(0,1); > > ERROR: function nvl(

Re: [GENERAL] Unprivileged access to pgsql functions?

On Fri, Mar 04, 2011 at 01:41:34PM -0800, Matt Warner wrote: > No luck: > > *** as postgres > postgres=# GRANT all on function nvl(anyelement,anyelement) to public; > GRANT > postgres=# > > *** as unprivileged user > offload=> select nvl(0,1); > ERROR: function nvl(integer, integer) does not exi

Re: [GENERAL] Unprivileged access to pgsql functions?

On Mar 5, 2011, at 3:11 AM, Matt Warner wrote: > postgres=# > > *** as unprivileged user > offload=> select nvl(0,1); > ERROR: function nvl(integer, integer) does not exist > LINE 1: select nvl(0,1); >^ > HINT: No function matches the given name and argument types. You might n

Re: [GENERAL] Unprivileged access to pgsql functions?

On 03/04/11 1:41 PM, Matt Warner wrote: No luck: *** as postgres postgres=# GRANT all on function nvl(anyelement,anyelement) to public; GRANT postgres=# *** as unprivileged user offload=> select nvl(0,1); ERROR: function nvl(integer, integer) does not exist LINE 1: select nvl(0,1);

Re: [GENERAL] Unprivileged access to pgsql functions?

Matt Warner wrote: > No luck: > > *** as postgres > postgres=# GRANT all on function nvl(anyelement,anyelement) to public; > GRANT > postgres=# > > *** as unprivileged user > offload=> select nvl(0,1); > ERROR: function nvl(integer, integer) does not exist > LINE 1: select nvl(0,1); >

Re: [GENERAL] Unprivileged access to pgsql functions?

Here's how the script is defining the function, if that helps: CREATE FUNCTION nvl(anyelement, anyelement) RETURNS anyelement AS '$libdir/orafunc','ora_nvl' LANGUAGE C IMMUTABLE; On Fri, Mar 4, 2011 at 1:41 PM, Matt Warner wrote: > No luck: > > *** as postgres > postgres=# GRANT all on function

Re: [GENERAL] Unprivileged access to pgsql functions?

No luck: *** as postgres postgres=# GRANT all on function nvl(anyelement,anyelement) to public; GRANT postgres=# *** as unprivileged user offload=> select nvl(0,1); ERROR: function nvl(integer, integer) does not exist LINE 1: select nvl(0,1); ^ HINT: No function matches the given

Re: [GENERAL] Unprivileged access to pgsql functions?

On Mar 5, 2011, at 2:50 AM, John R Pierce wrote: > On 03/04/11 1:11 PM, Matt Warner wrote: >> Good afternoon. >> >> I've been looking at the Oracle Functionality package. ... > > what is this? doesn't sound like anything in PostgreSQL I'm familiar with. > Is this part of EntepriseDB's Postgr

Re: [GENERAL] Unprivileged access to pgsql functions?

To be clear, this is open source Postgres I'm using, not the enterprise product. Matt On Fri, Mar 4, 2011 at 1:29 PM, Matt Warner wrote: > It's a "contrib" module: > > http://pgfoundry.org/projects/orafce/ > > Matt > > > On Fri, Mar 4, 2011 at 1:20 PM, John R Pierce wrote: > >> On 03/04/11 1:11

Re: [GENERAL] Unprivileged access to pgsql functions?

It's a "contrib" module: http://pgfoundry.org/projects/orafce/ Matt On Fri, Mar 4, 2011 at 1:20 PM, John R Pierce wrote: > On 03/04/11 1:11 PM, Matt Warner wrote: > >> Good afternoon. >> >> I've been looking at the Oracle Functionality package. ... >> > > what is this? doesn't sound like anyt

Re: [GENERAL] Unprivileged access to pgsql functions?

On 03/04/11 1:11 PM, Matt Warner wrote: Good afternoon. I've been looking at the Oracle Functionality package. ... what is this? doesn't sound like anything in PostgreSQL I'm familiar with. Is this part of EntepriseDB's Postgres+ package or something? You should probably contact them via

[GENERAL] Unprivileged access to pgsql functions?

Good afternoon. I've been looking at the Oracle Functionality package. It's very interesting. However, the one place I'm stuck is that while user Postgres can access the functions, no other user seems to have access. I'm sure this is something simple I'm missing, but so far Google hasn't shown me

Re: [GENERAL] I need your help to get opinions about this situation

Thanks to David and Greg, your responses helped me so much to organize my ideas. I'm agree with your opinions. The problem is that I have to build a solid data architecture for a comercial system that will have many reading queries and in some peak times many clients executing their. Thanks again.

Re: [GENERAL] script errors or PEBKAC?

On 03/04/2011 07:48 AM, Thufir Hawat wrote: I cannot get the script here: https://docs.google.com/leaf?id=0B5hKxkS1VyAxOGMzZjY4ZjktZjZkOS00Zjc3LWExYmEtYTU3ZThjYzZiMjk3&hl=en to run correctly. Looking at the output, many, many, errors, it seems to assume tables exist which don't. Is that correc

Re: [GENERAL] script errors or PEBKAC?

On 03/04/2011 07:48 AM, Thufir Hawat wrote: I cannot get the script here: https://docs.google.com/leaf?id=0B5hKxkS1VyAxOGMzZjY4ZjktZjZkOS00Zjc3LWExYmEtYTU3ZThjYzZiMjk3&hl=en to run correctly. Looking at the output, many, many, errors, it seems to assume tables exist which don't. Is that correc

Re: [GENERAL] script errors or PEBKAC?

I would check the order in which things are being created in your process. What I usually do is create all the tables sans indexing or constraints (except primary key). Once the table are done, load the data in to the tables that need to be populated. Then constraints, triggers and then index

Re: [GENERAL] database is bigger after dump/restore - why? (60 GB to 109 GB)

On Thursday, March 03, 2011 6:15:50 pm Aleksey Tsalolikhin wrote: > On Tue, Mar 1, 2011 at 7:24 AM, Tom Lane wrote: > > Adrian Klaver writes: > >> Looks like the TOAST compression is not working on the second machine. > >> Not sure how that could come to be. Further investigation underway:) > >

[GENERAL] script errors or PEBKAC?

I cannot get the script here: https://docs.google.com/leaf?id=0B5hKxkS1VyAxOGMzZjY4ZjktZjZkOS00Zjc3LWExYmEtYTU3ZThjYzZiMjk3&hl=en to run correctly. Looking at the output, many, many, errors, it seems to assume tables exist which don't. Is that correct? The script is supposed to create a databas

[GENERAL] gmane?

Looking at: Group gmane.comp.db.postgresql.general Description General discussion Address pgsql-general@... Status requires subscription to mailing list to post http://dir.gmane.org/gmane.comp.db.postgresql.general Is this info correct? thanks, Thufir -- Sent via pgsql-general mailing lis

Re: [GENERAL] updating all records of a table

I like the "cluster" and "reindex" ideas. The table is not that big and I do have the disk space. This table will also grow over time, so if the table ends up taking more space in the end, that's OK, it'll get used. The DB will also be unavailable to the users while this is happening, so I wo

Re: [GENERAL] How to select a list of sequences?

This might be helpful, as Raghavendrahave a look on relkind SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type", r.rolname as "Owner" FROM pg_cata

Re: [GENERAL] updating all records of a table

On 03/04/2011 04:54 AM, Vibhor Kumar wrote: > > On Mar 4, 2011, at 5:17 PM, Andrew Sullivan wrote: > >> On Thu, Mar 03, 2011 at 08:03:59PM -0700, Gauthier, Dave wrote: >>> Hi: >>> >>> I have to update all the records of a table. I'm worried about what the >>> table will look like in terms of

Re: [GENERAL] How to select a list of sequences?

Thank you. Please do add 'pgsql-general@postgresql.org' while replying. Best Regards, Raghavendra EnterpriseDB Corporation The Enterprise Postgres Company On Fri, Mar 4, 2011 at 8:04 PM, Kenneth Buckler wrote: > Perfect! Thanks a bunch! > > Ken > > On Fri, Mar 4, 2011 at 9:31 AM, Raghavendra >

Re: [GENERAL] How to select a list of sequences?

Hi Kenneth, You can retrieve the sequence information from the pg_catalog "pg_statio_all_sequences" or select * from pg_class where relkind='S' Best Regards, Raghavendra EnterpriseDB Corporation The Enterprise Postgres Company On Fri, Mar 4, 2011 at 7:32 PM, Kenneth Buckler wrote: > How can I

Re: [GENERAL] Screencasts for PostgreSQL

On Thu, March 3, 2011 09:18, Willy-Bas Loos wrote: > maybe this? > http://enterprisedb.com/resources-community/webcasts-podcasts-videos > > > cheers, > Thanks for the tip. I am taking a browse through these. -- ***

Re: [GENERAL] invalid byte sequence

Am 04.03.2011 um 11:01 schrieb Craig Ringer: > On 04/03/11 00:02, Maximilian Tyrtania wrote: >> After upgrading to pg 9.0.3 (from 8.4.2) on my Mac OS 10.6.2 machine i find >> this in my log file (a lot): >> >> STATEMENT: SELECT >> pg_file_read('pg_log/postgresql-2011-03-03_00.log', 25,

[GENERAL] How to select a list of sequences?

How can I select a list of sequences in Postgres 8.4? I'm writing functions which select names of tables, tablespaces, sequences, etc. For example, I can select a list of table names using the following command: SELECT tablename FROM pg_tables WHERE schemaname = 'public'; However, I can't seem

Re: [GENERAL] Pgdump error "invalid page header in block"

On Mar 4, 2011, at 1:17 PM, tuanhoanganh wrote: > pg_dump: reading dependency data > pg_dump: SQL command failed > pg_dump: Error message from server: ERROR: invalid page header in block 299 > of relation "pg_depend_depender_index" > pg_dump: The command was: SELECT classid, objid, refclassid,

Re: [GENERAL] updating all records of a table

On Mar 4, 2011, at 5:17 PM, Andrew Sullivan wrote: > On Thu, Mar 03, 2011 at 08:03:59PM -0700, Gauthier, Dave wrote: >> Hi: >> >> I have to update all the records of a table. I'm worried about what the >> table will look like in terms of fragmentation when this is finished. Is >> there some

Re: [GENERAL] updating all records of a table

On Thu, Mar 03, 2011 at 08:03:59PM -0700, Gauthier, Dave wrote: > Hi: > > I have to update all the records of a table. I'm worried about what the > table will look like in terms of fragmentation when this is finished. Is > there some sort of table healing/reorg/rebuild measure I should take if

[GENERAL] full text search

The full text search of postgres is not support Chinese, who can give me some advises?

Re: [GENERAL] invalid byte sequence

On 04/03/11 00:02, Maximilian Tyrtania wrote: > After upgrading to pg 9.0.3 (from 8.4.2) on my Mac OS 10.6.2 machine i find > this in my log file (a lot): > > STATEMENT: SELECT > pg_file_read('pg_log/postgresql-2011-03-03_00.log', 25, $ > ERROR: invalid byte > sequence for encoding "U

Re: [GENERAL] Pgdump error "invalid page header in block"

On 03/03/11 11:47 PM, tuanhoanganh wrote: Yesterday, I had some problem with postgresql 9.0.2. Today i backup postgres and has error pg_dump: reading dependency data pg_dump: SQL command failed pg_dump: Error message from server: ERROR: invalid page header in block 299 of relation "pg_depend_