Re: [GENERAL] split string by special characters
On 24 Juli, 23:22, a.w...@netzmeister-st-pauli.de (Andreas Wenk)
wrote:
Hi,
I was thinking about that and in my opinion the approach to let the
database do that is the wrong direction. Sure you can do a lot with
regexp_split_to_table or regexp_split_to_array but they are kind of
Yes, I see. You're quite right, the split was intended to do give me
everything in processed chunks it in some easy way as the last part of
the interpretation of the text.
limited compared to a programming language using regular expressions. If
I had to try to get your jobdone, I would try regexp_matches() like:
SELECT regexp_matches('This is just a text, that contain special
characters such as, (comma),(, ) (left and right parenthesis) as
well as ? question, mark.How do I split it up with PostgreSQL?',
E'(\\w*.)\\s+','g');
regexp_matches
{This}
{is}
{just}
{a}
{text,}
{that}
{contain}
{special}
{characters}
{such}
{as,}
{,}
{\}
{left}
{and}
{right}
{parenthesis)}
{as}
{well}
{as}
{\}
{question,}
{How}
{do}
{I}
{split}
{it}
{up}
{with}
(29 rows)
So, you have the ability to catch the seperators like ','. But for now,
teh example just catches the comma. But you want to catch a lot of other
Yes, but then I ran into the problems with separators that regexp
consider as part of the expression and how to dynamically build the
right expression in some unified way for each language.
seperators as well. I suggest you do that within the logic of your
coding language because I don't think this will be an easy way to walk
Guess you're right, because I didn't know how to handle it with the
regexp-approach.
I sat down yesterday and wrote a function that does the job for me in
PL/pgSQL, I'm not quite finished, but can see the light at the end of
the tunnel.
The basic approach I'm working with now is to let it find the position
of each delimiter combination within the text, then sort the resulting
array to get it ordered and extract each part.
It won't be fast as lightning, but sufficient for now and as it seem,
allow me to parse text from various files written in different
languages (e.g. programming) just by specifying the delimiters.
;-). This is no database job in my opinion.
I didn't intend to try it either before I spotted some of those
functions... :-)
Then figured it would be nice to do it within the db-engine as all the
data is present there.
I wrote code outside the db-engine some time ago, but then other
aspects made it less desirable to use.
Cheers
Andy
Thank you Andy for the code example and your advice.
I really appreciate that you took your time to show me how and explain
why.
//Jan-Erik
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Copying only incremental records to another DB..
Scott Ribe wrote: You mean rsync the data folder, or the entire PG folder? I meant the data folder. To be clearer: Do you mean that the folder you backed up is the folder with the file PG_VERSION in it, and all its contents? -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable databse listing for non-superuser (\l) ?
Scott Marlowe scott.marl...@gmail.com wrote: On Fri, Jul 24, 2009 at 5:02 PM, Brian A. Sekleckilaval...@spiritual-machines.org wrote: All: Any suggestions on how-to, or comments on a potential NFR, to disable non-superuser's from viewing the database list via \l? So, is this a misguided attempt at security through obscurity, or are you looking at limiting the noise that users see when they look at databases? I don't know about misguided, Scott. Security takes many forms. If a client wants shared database hosting, but wants an assurance that other clients using the same shared DB server can't tell who else is using it? It's not security in the strict computer-science definition. Obviously, if the proper ownerships and grants don't exist to protect the data, in addition to said obscurity, then the whole thing is pointless. But such obscurity _in_addition_ to proper, real security, has show usefulness in many areas. Take a properly secured SSH server, for example, and move it to an obscure port #. Now you've reduced the number of mindless bots looking for unprotected root accounts, and your IDS solution that monitors the ssh logs is actually useful. Of course, that's only effective if ssh is properly secured to begin with. Similar concept. Many clients want the cost-effectiveness of shared DB hosting. Many of them also want it kept under wraps that they're doing so. The provider that can do such a thing gets the contract. Those that complain about it's not security, it's obscurity do not get the contract. I mean, didn't Apple just kill someone for letting their new iPhone design leak? -- Bill Moran http://www.potentialtech.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Very slow joins
can we see an explain analyze at least? Hi, Well, it won't be necessary - I mean it looks just like the explain I sent in my first post. BUT I found the real cause of my problem - the fk2 field from my example had not only an index, but it was also a foreign key to another table. I believe the update took so long because pgsql was checking if the changes don't break the referential integrity. When I dropped the FK constraint (and index too - just in case) the update took around 3 minutes which is acceptable. So - problem solved, postgres good. ;) But isn't there a way to make some bulk operations without having to drop indexes/FKs? Something that would work like: begin transaction + forget about RI make some lenghty operation (update/delete...) if RI is OK then commit; else rollback Thanks, MS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is there a RECORD[] type in plpgsql?
Im using the same... FOR record_or_row IN query LOOP at the beginning of a function and at the end of a function. Is there a way to save the query results in a RECORD[] type so that I don't have to run the query twice? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Very slow joins
On 25 Jul 2009, at 11:36, MS wrote: can we see an explain analyze at least? Hi, Well, it won't be necessary - I mean it looks just like the explain I sent in my first post. What first post? The only thing I can find is a reference in a message by you from yesterday, to a two-year old post that you claim is about the same problem. Though it's possible that it is the same problem, you don't provide any data to back that up. The message you referred to was about a one-of-a-kind problem with communications to the client and had nothing to do with performance on the server; is that indeed what you're seeing? In that case you should check your network infrastructure for problems. Usually server performance problems are due to problems with tuning parameters or outdated statistics. Those issues can usually be solved easily. Without posting an EXPLAIN ANALYSE people here can only guess what your problem is. BUT I found the real cause of my problem - the fk2 field from my example had not only an index, but it was also a foreign key to another table. I believe the update took so long because pgsql was checking if the changes don't break the referential integrity. When I dropped the FK constraint (and index too - just in case) the update took around 3 minutes which is acceptable. So - problem solved, postgres good. ;) But isn't there a way to make some bulk operations without having to drop indexes/FKs? Something that would work like: begin transaction + forget about RI make some lenghty operation (update/delete...) if RI is OK then commit; else rollback That seems unlikely to be the cause. From the above it seems much more likely that you're suffering from a bad query plan instead, but you don't provide any details. Disabling referential integrity is a bad thing to do, and very rarely necessary. But we don't know what you're trying to do, except that you're updating some records that apparently have a foreign key reference. It would also help to know what version of PostgreSQL this is and on what hardware and setup you're running into this issue. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a6af5d410132049512701! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Very slow joins
On Sat, Jul 25, 2009 at 02:36:19AM -0700, MS wrote: I believe the update took so long because pgsql was checking if the changes don't break the referential integrity. So - problem solved, postgres good. ;) But isn't there a way to make some bulk operations without having to drop indexes/FKs? I've never had the need to use this, but I believe this works using the SET CONSTRAINTS command[1]; e.g. I can do: CREATE TABLE foo ( id INTEGER PRIMARY KEY ); CREATE TABLE bar ( id INTEGER REFERENCES foo DEFERRABLE ); INSERT INTO foo VALUES (1); INSERT INTO bar VALUES (1); the following will now fail: BEGIN; INSERT INTO bar VALUES (2); INSERT INTO foo VALUES (2); COMMIT; but the following is OK: BEGIN; SET CONSTRAINTS bar_id_fkey DEFERRED; INSERT INTO bar VALUES (2); INSERT INTO foo VALUES (2); COMMIT; Unfortunatly only foreign key constraints are affected by this setting, but I believe there are plans to extend this further. -- Sam http://samason.me.uk/ http://www.postgresql.org/docs/current/static/sql-set-constraints.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there a RECORD[] type in plpgsql?
On Fri, Jul 24, 2009 at 11:42:07PM -0700, Nick Boutelier wrote: Im using the same... FOR record_or_row IN query LOOP at the beginning of a function and at the end of a function. Is there a way to save the query results in a RECORD[] type so that I don't have to run the query twice? Wouldn't that be a temporary table? -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there a RECORD[] type in plpgsql?
On Sat, Jul 25, 2009 at 2:42 AM, Nick Bouteliernamethis...@gmail.com wrote: Im using the same... FOR record_or_row IN query LOOP at the beginning of a function and at the end of a function. Is there a way to save the query results in a RECORD[] type so that I don't have to run the query twice? yes (in postgresql 8.3). This is often a little more convenient than a temporary table. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable databse listing for non-superuser (\l) ?
Bill Moran schrieb: Scott Marlowe scott.marl...@gmail.com wrote: On Fri, Jul 24, 2009 at 5:02 PM, Brian A. Sekleckilaval...@spiritual-machines.org wrote: All: Any suggestions on how-to, or comments on a potential NFR, to disable non-superuser's from viewing the database list via \l? So, is this a misguided attempt at security through obscurity, or are you looking at limiting the noise that users see when they look at databases? I don't know about misguided, Scott. Security takes many forms. If a client wants shared database hosting, but wants an assurance that other clients using the same shared DB server can't tell who else is using it? It's not security in the strict computer-science definition. Obviously, if the proper ownerships and grants don't exist to protect the data, in addition to said obscurity, then the whole thing is pointless. But such obscurity _in_addition_ to proper, real security, has show usefulness in many areas. Take a properly secured SSH server, for example, and move it to an obscure port #. Now you've reduced the number of mindless bots looking for unprotected root accounts, and your IDS solution that monitors the ssh logs is actually useful. Of course, that's only effective if ssh is properly secured to begin with. Similar concept. Many clients want the cost-effectiveness of shared DB hosting. Many of them also want it kept under wraps that they're doing so. The provider that can do such a thing gets the contract. Those that complain about it's not security, it's obscurity do not get the contract. I mean, didn't Apple just kill someone for letting their new iPhone design leak? this is now going off topic - but what do you mean with your last sentence? Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] split string by special characters
Jan-Erik schrieb:
On 24 Juli, 23:22, a.w...@netzmeister-st-pauli.de (Andreas Wenk)
wrote:
Hi,
I was thinking about that and in my opinion the approach to let the
database do that is the wrong direction. Sure you can do a lot with
regexp_split_to_table or regexp_split_to_array but they are kind of
Yes, I see. You're quite right, the split was intended to do give me
everything in processed chunks it in some easy way as the last part of
the interpretation of the text.
limited compared to a programming language using regular expressions. If
I had to try to get your jobdone, I would try regexp_matches() like:
SELECT regexp_matches('This is just a text, that contain special
characters such as, (comma),(, ) (left and right parenthesis) as
well as ? question, mark.How do I split it up with PostgreSQL?',
E'(\\w*.)\\s+','g');
regexp_matches
{This}
{is}
{just}
{a}
{text,}
{that}
{contain}
{special}
{characters}
{such}
{as,}
{,}
{\}
{left}
{and}
{right}
{parenthesis)}
{as}
{well}
{as}
{\}
{question,}
{How}
{do}
{I}
{split}
{it}
{up}
{with}
(29 rows)
So, you have the ability to catch the seperators like ','. But for now,
teh example just catches the comma. But you want to catch a lot of other
Yes, but then I ran into the problems with separators that regexp
consider as part of the expression and how to dynamically build the
right expression in some unified way for each language.
seperators as well. I suggest you do that within the logic of your
coding language because I don't think this will be an easy way to walk
Guess you're right, because I didn't know how to handle it with the
regexp-approach.
I sat down yesterday and wrote a function that does the job for me in
PL/pgSQL, I'm not quite finished, but can see the light at the end of
the tunnel.
just in case you are running into a black performance hole - you could
try to write it in C as a user defined function. Actually for me it
would be a real big challenge ;-)
The basic approach I'm working with now is to let it find the position
of each delimiter combination within the text, then sort the resulting
array to get it ordered and extract each part.
It won't be fast as lightning, but sufficient for now and as it seem,
allow me to parse text from various files written in different
languages (e.g. programming) just by specifying the delimiters.
;-). This is no database job in my opinion.
I didn't intend to try it either before I spotted some of those
functions... :-)
Then figured it would be nice to do it within the db-engine as all the
data is present there.
I wrote code outside the db-engine some time ago, but then other
aspects made it less desirable to use.
Cheers
Andy
Thank you Andy for the code example and your advice.
I really appreciate that you took your time to show me how and explain
why.
//Jan-Erik
hey you're welcome. When you're done it would be really great to see the
resulting function ;-)
Cheers
Andy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable databse listing for non-superuser (\l) ?
On Sat, Jul 25, 2009 at 2:53 PM, Andreas Wenka.w...@netzmeister-st-pauli.de wrote: I mean, didn't Apple just kill someone for letting their new iPhone design leak? this is now going off topic - but what do you mean with your last sentence? Please don't quote an entire message if you're only responding to part of it. (That's what people are really talking about when they say top-posting is bad.) see: http://www.theregister.co.uk/2009/07/22/security_offical_suspended/ I don't think there's any actual indication that Apple was involved directly. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Very slow joins
On Sat, Jul 25, 2009 at 8:45 AM, Sam Masons...@samason.me.uk wrote: On Sat, Jul 25, 2009 at 02:36:19AM -0700, MS wrote: I believe the update took so long because pgsql was checking if the changes don't break the referential integrity. So - problem solved, postgres good. ;) But isn't there a way to make some bulk operations without having to drop indexes/FKs? I've never had the need to use this, but I believe this works using the SET CONSTRAINTS command[1]; e.g. I can do: CREATE TABLE foo ( id INTEGER PRIMARY KEY ); CREATE TABLE bar ( id INTEGER REFERENCES foo DEFERRABLE ); INSERT INTO foo VALUES (1); INSERT INTO bar VALUES (1); the following will now fail: BEGIN; INSERT INTO bar VALUES (2); INSERT INTO foo VALUES (2); COMMIT; but the following is OK: BEGIN; SET CONSTRAINTS bar_id_fkey DEFERRED; INSERT INTO bar VALUES (2); INSERT INTO foo VALUES (2); COMMIT; Unfortunatly only foreign key constraints are affected by this setting, but I believe there are plans to extend this further. You can also disable triggers completely: begin; alter table foo disable trigger all; do stuff alter table foo enable trigger all; commit; of course, if you do this the data is never checked at all, so you have to be super careful with it merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Server/Client Encoding Errors
Hey,
I'm having some problems when inserting special characters into a
column. Here's the table:
--
Table public.users_history_ip
Column |Type | Modifiers
+-+---
id | bigint | not null default
nextval('users_history_ip_id_seq'::regclass)
userid | integer | not null
ip | inet| not null
hostname | character varying(512) | not null
geoip_info | character varying(512) | not null
start_time | timestamp without time zone | not null
last_seen | timestamp without time zone | not null
type | ip_history_type | not null
Indexes:
users_history_ip_pkey PRIMARY KEY, btree (id)
Foreign-key constraints:
users_history_ip_userid_fkey FOREIGN KEY (userid) REFERENCES
users_main(id) ON DELETE CASCADE
--
I'm trying to insert information into the geoip_info column. Here's
some of the information that I'm trying to insert, and the errors:
'Portugal, 09, Vila Real De Santo António'
ERROR: invalid byte sequence for encoding UTF8: 0xf36e696f
'Norway, 08, Ålesund'
ERROR: invalid byte sequence for encoding UTF8: 0xc56c
'Portugal, 04, Vila Nova De Famalicão'
ERROR: invalid byte sequence for encoding UTF8: 0xe36f2c
The locale on the server is C and the encoding is UTF8. I thought
the UTF8 encoding would allow characters like this? Why is it
disallowing it?
Note, the GeoIP info is generated automatically by a module, so I am
unable to determine exactly what characters will be returned.
Thanks for the help.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Copying only incremental records to another DB..
Craig Ringer cr...@postnewspapers.com.au writes: To be clearer: Do you mean that the folder you backed up is the folder with the file PG_VERSION in it, and all its contents? Careful --- there are multiple PG_VERSION files scattered around in a Postgres data directory tree. Your comment is correct with respect to the topmost one, but I'm not certain it's a foolproof definition of the data directory. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] FATAL: root page 3 of pg_class_oid_index has level 0, expected 1
Andrew Radamis rust...@gmail.com writes: ERROR: invalid page header in block 43 of relation pg_attribute At this point I think you should assume you've got serious hardware problems. Get hold of some memory and disk test programs and see what you can find out. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable databse listing for non-superuser (\l) ?
Greg Stark schrieb: On Sat, Jul 25, 2009 at 2:53 PM, Andreas Wenka.w...@netzmeister-st-pauli.de wrote: I mean, didn't Apple just kill someone for letting their new iPhone design leak? this is now going off topic - but what do you mean with your last sentence? Please don't quote an entire message if you're only responding to part of it. (That's what people are really talking about when they say top-posting is bad.) see: http://www.theregister.co.uk/2009/07/22/security_offical_suspended/ I don't think there's any actual indication that Apple was involved directly. no problem and got it - but my reply was kind of a quick shot because I don't think that posting messages /sentences like that are really useful or helpful for the community ... Bill, don't get me wrong - this is my personal opinion. Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Content-Type in form variables
If I receive a form via POST or PUT with with mulitple variables, files,
application/json, others, is there anywhere in the environment to test he mime
type of each variable?
POST /en/html/dummy.php HTTP/1.1
Host: www.explainth.at
User-Agent: Mozilla/5.0 (Windows;en-GB; rv:1.8.0.11) Gecko/20070312
Firefox/1.5.0.11
Accept: text/xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5
Accept-Language: en-gb,en;q=0.5
Accept-Encoding: gzip,deflate
Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.7
Keep-Alive: 300
Connection: keep-alive
Referer: http://www.explainth.at/en/misc/httpreq.shtml?txt=
Content-Type: multipart/form-data;
boundary=---103832778631715
Content-Length: 355
-103832778631715
Content-Disposition: form-data; name=JSON_OBJS
Content-Type: application/json
{
variable_name: 8675309
blob_value: NULL
}
-103832778631715
Content-Disposition: form-data; name=blob_value; filename=previous.png
Content-Type: image/png
PNG
-103832778631715
Dennis Gearon
Signature Warning
EARTH has a Right To Life
I agree with Bolivian President Evo Morales
# The right to life: The right for no ecosystem to be eliminated by the
irresponsible acts of human beings.
# The right of biosystems to regenerate themselves: Development cannot be
infinite. There's a limit on everything.
# The right to a clean life: The right for Mother Earth to live without
contamination, pollution. Fish and animals and trees have rights.
# The right to harmony and balance between everyone and everything: We are all
interdependent.
See the movie - 'Inconvenient Truth'
See the movie - 'Syriana'
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] where is pg_resetxlog ?
Hi all; I'm trying to restore from a tar of the filesystem on a debian box and I get xlog errors. I suspect I need to run pg_resetxlog but I cannot find it anywhere, where would I find pg_resetxlog on a debian box that was installed via the deb packages ? or how do I get it ? Thanks in advance.. here's my log startup messages in case it helps: Jul 25 18:40:17 ux-dbs01-atl2 postgres[16042]: [1-1] time:2009-07-25 18:40:17.446 UTC database: LOG: could not load root certificate file root.crt: no SSL error reported Jul 25 18:40:17 ux-dbs01-atl2 postgres[16042]: [1-2] time:2009-07-25 18:40:17.446 UTC database: DETAIL: Will not verify client certificates. Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [2-1] time:2009-07-25 18:40:17.653 UTC database: LOG: database system was shut down at 2009-07-23 01:18:04 UTC Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [3-1] time:2009-07-25 18:40:17.653 UTC database: LOG: could not open file pg_xlog/000104B600C7 (log file 1206, segment Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [3-2] 199): No such file or directory Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [4-1] time:2009-07-25 18:40:17.653 UTC database: LOG: invalid primary checkpoint record Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [5-1] time:2009-07-25 18:40:17.653 UTC database: LOG: could not open file pg_xlog/000104B600C7 (log file 1206, segment Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [5-2] 199): No such file or directory Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [6-1] time:2009-07-25 18:40:17.653 UTC database: LOG: invalid secondary checkpoint record Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [7-1] time:2009-07-25 18:40:17.653 UTC database: PANIC: could not locate a valid checkpoint record Jul 25 18:40:17 ux-dbs01-atl2 postgres[16042]: [2-1] time:2009-07-25 18:40:17.653 UTC database: LOG: startup process (PID 16043) was terminated by signal 6: Aborted Jul 25 18:40:17 ux-dbs01-atl2 postgres[16042]: [3-1] time:2009-07-25 18:40:17.653 UTC database: LOG: aborting startup due to startup process failure -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] where is pg_resetxlog ?
On Sat, Jul 25, 2009 at 12:55 PM, Kevin Kempterkev...@consistentstate.com wrote: Hi all; I'm trying to restore from a tar of the filesystem on a debian box and I get xlog errors. I suspect I need to run pg_resetxlog but I cannot find it anywhere, where would I find pg_resetxlog on a debian box that was installed via the deb packages ? or how do I get it ? Wait, if you're restoring a backup, to a freshly initted db, then you shouldn't be getting any kind of pg_xlog errors. If you are, then there's likely something wrong with your server that pg_resetxlog isn't going to fix in the long term. Are you running on windows with anti-virus software or have some other kind of possible problem that could be causing a problem with the postmaster writing to the hard drives? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable databse listing for non-superuser (\l) ?
On Sat, Jul 25, 2009 at 5:23 AM, Bill Moranwmo...@potentialtech.com wrote: Scott Marlowe scott.marl...@gmail.com wrote: On Fri, Jul 24, 2009 at 5:02 PM, Brian A. Sekleckilaval...@spiritual-machines.org wrote: All: Any suggestions on how-to, or comments on a potential NFR, to disable non-superuser's from viewing the database list via \l? So, is this a misguided attempt at security through obscurity, or are you looking at limiting the noise that users see when they look at databases? I don't know about misguided, Scott. Security takes many forms. If a client wants shared database hosting, but wants an assurance that other clients using the same shared DB server can't tell who else is using it? Then they want something other than security. Which isn't necessarily a bad thing, just don't fool yourself into thinking it's security. It's not security in the strict computer-science definition. Obviously, if the proper ownerships and grants don't exist to protect the data, in addition to said obscurity, then the whole thing is pointless. exactly. But such obscurity _in_addition_ to proper, real security, has show usefulness in many areas. Citation needed. I doubt it's ever made any real measurable difference. Take a properly secured SSH server, for example, and move it to an obscure port #. Now you've reduced the number of mindless bots looking for unprotected root accounts, and your IDS solution that monitors the ssh logs is actually useful. Of course, that's only effective if ssh is properly secured to begin with. If it's secure, then it doesn't matter what port it's on. If it's not secure, being on a secondary port is no great improvement. Many clients want the cost-effectiveness of shared DB hosting. Many of them also want it kept under wraps that they're doing so. The provider that can do such a thing gets the contract. Those that complain about it's not security, it's obscurity do not get the contract. Yep. And i can guarantee that having such a contract mens you've got a customer that makes you wanna pull your hair out. Having dealt with a few like that in the past. :) But my very serious point on this is that postgresql isnt' designed to hide such things from users, and changing it to do so takes a lot of effort for no real return on investment. OTOH, having a psql client that just uses a different set of queries so that it doesn't show the other dbs could be actually useful and take little or no effort. Given the lack of a serious clarification or answer from OP, I've not been inclined to post anymore on this subject. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] where is pg_resetxlog ?
On Saturday 25 July 2009 13:02:52 Scott Marlowe wrote: On Sat, Jul 25, 2009 at 12:55 PM, Kevin Kempterkev...@consistentstate.com wrote: Hi all; I'm trying to restore from a tar of the filesystem on a debian box and I get xlog errors. I suspect I need to run pg_resetxlog but I cannot find it anywhere, where would I find pg_resetxlog on a debian box that was installed via the deb packages ? or how do I get it ? Wait, if you're restoring a backup, to a freshly initted db, then you shouldn't be getting any kind of pg_xlog errors. If you are, then there's likely something wrong with your server that pg_resetxlog isn't going to fix in the long term. Are you running on windows with anti-virus software or have some other kind of possible problem that could be causing a problem with the postmaster writing to the hard drives? we're not restoring from a pg_dump. We were in the process of moving the db to a new server. We brought the db down (on host A) and did an rsync of all the db dir's (including tablespace dir's) to host B. Then bad things were done to host A and we want to get back to where we started. So, we stopped the db on host A and rsync'ed the files back from host B to host A. Now when I try and start the db I see all these tx sement errors in the log. We're actually ok if we loose anything that was in the pg_xlog dir. Thoughts? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] where is pg_resetxlog ?
On Sat, Jul 25, 2009 at 1:08 PM, Kevin Kempterkev...@consistentstate.com wrote: On Saturday 25 July 2009 13:02:52 Scott Marlowe wrote: On Sat, Jul 25, 2009 at 12:55 PM, Kevin Kempterkev...@consistentstate.com wrote: Hi all; I'm trying to restore from a tar of the filesystem on a debian box and I get xlog errors. I suspect I need to run pg_resetxlog but I cannot find it anywhere, where would I find pg_resetxlog on a debian box that was installed via the deb packages ? or how do I get it ? Wait, if you're restoring a backup, to a freshly initted db, then you shouldn't be getting any kind of pg_xlog errors. If you are, then there's likely something wrong with your server that pg_resetxlog isn't going to fix in the long term. Are you running on windows with anti-virus software or have some other kind of possible problem that could be causing a problem with the postmaster writing to the hard drives? we're not restoring from a pg_dump. We were in the process of moving the db to a new server. We brought the db down (on host A) and did an rsync of all the db dir's (including tablespace dir's) to host B. Then bad things were done to host A and we want to get back to where we started. So, we stopped the db on host A and rsync'ed the files back from host B to host A. Now when I try and start the db I see all these tx sement errors in the log. We're actually ok if we loose anything that was in the pg_xlog dir. Did you rsync the pg_xlog and pg_clog (i.e. EVERYTHING under data/) or just the base directory? You really need to do an rsync of everything, not most everything. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] where is pg_resetxlog ?
On Saturday 25 July 2009 13:23:54 Scott Marlowe wrote: On Sat, Jul 25, 2009 at 1:08 PM, Kevin Kempterkev...@consistentstate.com wrote: On Saturday 25 July 2009 13:02:52 Scott Marlowe wrote: On Sat, Jul 25, 2009 at 12:55 PM, Kevin Kempterkev...@consistentstate.com wrote: Hi all; I'm trying to restore from a tar of the filesystem on a debian box and I get xlog errors. I suspect I need to run pg_resetxlog but I cannot find it anywhere, where would I find pg_resetxlog on a debian box that was installed via the deb packages ? or how do I get it ? Wait, if you're restoring a backup, to a freshly initted db, then you shouldn't be getting any kind of pg_xlog errors. If you are, then there's likely something wrong with your server that pg_resetxlog isn't going to fix in the long term. Are you running on windows with anti-virus software or have some other kind of possible problem that could be causing a problem with the postmaster writing to the hard drives? we're not restoring from a pg_dump. We were in the process of moving the db to a new server. We brought the db down (on host A) and did an rsync of all the db dir's (including tablespace dir's) to host B. Then bad things were done to host A and we want to get back to where we started. So, we stopped the db on host A and rsync'ed the files back from host B to host A. Now when I try and start the db I see all these tx sement errors in the log. We're actually ok if we loose anything that was in the pg_xlog dir. Did you rsync the pg_xlog and pg_clog (i.e. EVERYTHING under data/) or just the base directory? You really need to do an rsync of everything, not most everything. we rsync'ed EVERYTHING (the entire directory and all sub-dirs) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using unnest
Hi, I was trying to use information from the pg_stats view, when I remembered that 8.4 now has the cool unnest function. However I can't seem to get this to work with a column defined as anyarray. So my query is: select histogram_bounds from pg_stats where tablename = 'my_table' and attname = 'col1'; Now I would like to get the elements of the histogram_bounds column as a set, and thought that unnest would help me here, but the following: select unnest(histogram_bounds) from pg_stats where tablename = 'my_table' and attname = 'col1'; gives me the error argument declared anyarray is not an array but type anyarray I'm pretty sure I'm overlooking something obvious with regards to the unnest syntax, but what? Thanks Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using unnest
On Sat, 2009-07-25 at 22:24 +0200, Thomas Kellerer wrote: I was trying to use information from the pg_stats view, when I remembered that 8.4 now has the cool unnest function. However I can't seem to get this to work with a column defined as anyarray. It's generally hard to work with values of type anyarray. You have to cast them to text and then to a normal array type. For example: select unnest(histogram_bounds::text::oid[]) from pg_stats where tablename='pg_amop' and attname='amopopr'; Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using unnest
Jeff Davis wrote on 25.07.2009 22:44:
It's generally hard to work with values of type anyarray. You have to
cast them to text and then to a normal array type.
For example:
select unnest(histogram_bounds::text::oid[]) from pg_stats where
tablename='pg_amop' and attname='amopopr';
Great, thanks
I tried casting the column to text[] (because it contains elements of type text) but I didn't think
of doing a two way cast.
Do I understand this correctly that by casting it first to text, I effectively create a new array
the same way I create one, when I supply a literal like '{1,2,3}'::text[]
Thomas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] where is pg_resetxlog ?
Kevin Kempter wrote: I suspect I need to run pg_resetxlog but I cannot find it anywhere, where would I find pg_resetxlog on a debian box that was installed via the deb packages ? or how do I get it ? It's under /usr/lib/postgresql/8.3/bin Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using unnest
On Sat, 2009-07-25 at 22:54 +0200, Thomas Kellerer wrote:
Do I understand this correctly that by casting it first to text, I
effectively create a new array
the same way I create one, when I supply a literal like '{1,2,3}'::text[]
Similar, but not quite the same. '{1,2,3}'::text[] is actually
constructing from the cstring type using the type input function.
Cstring is not a normal type, it is what things are before they have a
normal type.
So, my strategy will only work if the array type you're trying to cast
to has a cast from text.
There aren't separate input functions for each array type, so it's hard
to make this work without depending on a cast from text. Perhaps someone
else has a better idea, though.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] where is pg_resetxlog ?
Kevin Kempter kev...@consistentstate.com writes: On Saturday 25 July 2009 13:23:54 Scott Marlowe wrote: Did you rsync the pg_xlog and pg_clog (i.e. EVERYTHING under data/) or just the base directory? You really need to do an rsync of everything, not most everything. we rsync'ed EVERYTHING (the entire directory and all sub-dirs) Those log entries are proof positive that you forgot pg_xlog. Maybe you had pg_xlog/ symlinked to someplace else on the old server and forgot to replicate that arrangement on the new one? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using unnest
Jeff Davis pg...@j-davis.com writes: So, my strategy will only work if the array type you're trying to cast to has a cast from text. As of (IIRC) 8.3, every type does have a cast from text. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Content-Type in form variables
On Sat, Jul 25, 2009 at 11:12:15AM -0700, Dennis Gearon wrote: If I receive a form via POST or PUT with with mulitple variables, files, application/json, others, is there anywhere in the environment to test he mime type of each variable? ?? this doesn't seem particularly related to PG! Anyway... mime-types are conventionally associated with files, not variables. if you want to do input validation maybe you could write (or find) a parser? a set of regexs are normally easy and formal enough for simple purposes. Most languages contain code for parsing mime encoded documents; python makes this sort of thing reasonably easy if you want to stay reasonably low level or there are lots of frameworks around to simplify things. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable databse listing for non-superuser (\l) ?
Scott Marlowe scott.marl...@gmail.com wrote: On Sat, Jul 25, 2009 at 5:23 AM, Bill Moranwmo...@potentialtech.com wrote: Scott Marlowe scott.marl...@gmail.com wrote: On Fri, Jul 24, 2009 at 5:02 PM, Brian A. Sekleckilaval...@spiritual-machines.org wrote: All: Any suggestions on how-to, or comments on a potential NFR, to disable non-superuser's from viewing the database list via \l? So, is this a misguided attempt at security through obscurity, or are you looking at limiting the noise that users see when they look at databases? I don't know about misguided, Scott. Security takes many forms. If a client wants shared database hosting, but wants an assurance that other clients using the same shared DB server can't tell who else is using it? Then they want something other than security. Which isn't necessarily a bad thing, just don't fool yourself into thinking it's security. To be fair, the OP didn't say it was for security purposes. It's not security in the strict computer-science definition. Obviously, if the proper ownerships and grants don't exist to protect the data, in addition to said obscurity, then the whole thing is pointless. exactly. But such obscurity _in_addition_ to proper, real security, has show usefulness in many areas. Citation needed. I doubt it's ever made any real measurable difference. Well, I did mention one -- the ssh example below. Take a properly secured SSH server, for example, and move it to an obscure port #. Now you've reduced the number of mindless bots looking for unprotected root accounts, and your IDS solution that monitors the ssh logs is actually useful. Of course, that's only effective if ssh is properly secured to begin with. If it's secure, then it doesn't matter what port it's on. If it's not secure, being on a secondary port is no great improvement. I'm surprised how it seems that very few people are familiar with IDS and auditing functions. If you have an IDS or need to audit suspicious activity as part of your security posture (which we _do_ and everyone _should_) then anything you can do to reduce the amount of false positives (i.e. noise) coming through the IDS or audit system, makes your IDS/audit process more efficient. And with a more efficient audit process, you're more likely to identify real threats, thus your system is more secure. Many clients want the cost-effectiveness of shared DB hosting. Many of them also want it kept under wraps that they're doing so. The provider that can do such a thing gets the contract. Those that complain about it's not security, it's obscurity do not get the contract. Yep. And i can guarantee that having such a contract mens you've got a customer that makes you wanna pull your hair out. Having dealt with a few like that in the past. :) How many clients do you have that don't result in hair pulling? If it were easy, it'd be difficult to get paid for it. But my very serious point on this is that postgresql isnt' designed to hide such things from users, and changing it to do so takes a lot of effort for no real return on investment. OTOH, having a psql client that just uses a different set of queries so that it doesn't show the other dbs could be actually useful and take little or no effort. Given the lack of a serious clarification or answer from OP, I've not been inclined to post anymore on this subject. I work with the OP, and his post was the result of discussions we had earlier this week on how we can take our security posture to the next level. I warned him about asking this on the list ... knowing that the assumption would be that we're doing this as a substitute for real security. The reality is that we've implemented all the other standard methods, this is the result of our continual effort to find ways to lock things down even tighter. -- Bill Moran http://www.potentialtech.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Very slow joins
Alban Hertroys wrote: On 25 Jul 2009, at 11:36, MS wrote: can we see an explain analyze at least? Hi, Well, it won't be necessary - I mean it looks just like the explain I sent in my first post. What first post? The only thing I can find is a reference in a message by you from yesterday, to a two-year old post that you claim is about the same problem. Though it's possible that it is the same problem, you don't provide any data to back that up. Yeah I'm confused too. The first post in this thread that I recieved was the same one you mention and began with a Re: in the subject line as if it wasn't the first message, but I can find no sign of a message prior to it. Was this a cross-post where the thread started in another group? Or did something go awry with the listserv and the first post or three get lost? Eric -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] where is pg_resetxlog ?
On Sat, Jul 25, 2009 at 1:30 PM, Kevin Kempterkev...@consistentstate.com wrote: On Saturday 25 July 2009 13:23:54 Scott Marlowe wrote: On Sat, Jul 25, 2009 at 1:08 PM, Kevin Kempterkev...@consistentstate.com wrote: On Saturday 25 July 2009 13:02:52 Scott Marlowe wrote: On Sat, Jul 25, 2009 at 12:55 PM, Kevin Kempterkev...@consistentstate.com wrote: Hi all; I'm trying to restore from a tar of the filesystem on a debian box and I get xlog errors. I suspect I need to run pg_resetxlog but I cannot find it anywhere, where would I find pg_resetxlog on a debian box that was installed via the deb packages ? or how do I get it ? Wait, if you're restoring a backup, to a freshly initted db, then you shouldn't be getting any kind of pg_xlog errors. If you are, then there's likely something wrong with your server that pg_resetxlog isn't going to fix in the long term. Are you running on windows with anti-virus software or have some other kind of possible problem that could be causing a problem with the postmaster writing to the hard drives? we're not restoring from a pg_dump. We were in the process of moving the db to a new server. We brought the db down (on host A) and did an rsync of all the db dir's (including tablespace dir's) to host B. Then bad things were done to host A and we want to get back to where we started. So, we stopped the db on host A and rsync'ed the files back from host B to host A. Now when I try and start the db I see all these tx sement errors in the log. We're actually ok if we loose anything that was in the pg_xlog dir. Did you rsync the pg_xlog and pg_clog (i.e. EVERYTHING under data/) or just the base directory? You really need to do an rsync of everything, not most everything. we rsync'ed EVERYTHING (the entire directory and all sub-dirs) OK, on my laptop, in the /var/lib/postgresql/8.3/main there's a bunch of dirs that look like this: basepg_clog pg_subtrans pg_twophase pg_xlog postmaster.pid server.crt global pg_multixact pg_tblspcPG_VERSION postmaster.opts root.crtserver.key Did you rsync the base dir only, or all of the directories above? Cause if you just rsynced base, you need the rest of them, as well. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Content-Type in form variables
Sorry, pgsql-general? php-general? who can tell the difference while your eyes are still focusing from long hours on the comptuer? Sorry guys. PS, no wonder it didn't show up or get answered from the php guys ;-) Sam Mason s...@samason.me.ukwrote Subject: Re: Content-Type in form variables Message-ID: 20090726012447.gq5...@samason.me.uk On Sat, Jul 25, 2009 at 11:12:15AM -0700, Dennis Gearon wrote: If I receive a form via POST or PUT with with mulitple variables, files, application/json, others, is there anywhere in the environment to test he mime type of each variable? ?? this doesn't seem particularly related to PG! Anyway... mime-types are conventionally associated with files, not variables. if you want to do input validation maybe you could write (or find) a parser? a set of regexs are normally easy and formal enough for simple purposes. Most languages contain code for parsing mime encoded documents; python makes this sort of thing reasonably easy if you want to stay reasonably low level or there are lots of frameworks around to simplify things. -- Sam http://samason.me.uk/ -- End of [pgsql-general] Daily digest v1.9201 (20 messages) ** -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] where is pg_resetxlog ?
On Saturday 25 July 2009 20:05:14 Scott Marlowe wrote: On Sat, Jul 25, 2009 at 1:30 PM, Kevin Kempterkev...@consistentstate.com wrote: On Saturday 25 July 2009 13:23:54 Scott Marlowe wrote: On Sat, Jul 25, 2009 at 1:08 PM, Kevin Kempterkev...@consistentstate.com wrote: On Saturday 25 July 2009 13:02:52 Scott Marlowe wrote: On Sat, Jul 25, 2009 at 12:55 PM, Kevin Kempterkev...@consistentstate.com wrote: Hi all; I'm trying to restore from a tar of the filesystem on a debian box and I get xlog errors. I suspect I need to run pg_resetxlog but I cannot find it anywhere, where would I find pg_resetxlog on a debian box that was installed via the deb packages ? or how do I get it ? Wait, if you're restoring a backup, to a freshly initted db, then you shouldn't be getting any kind of pg_xlog errors. If you are, then there's likely something wrong with your server that pg_resetxlog isn't going to fix in the long term. Are you running on windows with anti-virus software or have some other kind of possible problem that could be causing a problem with the postmaster writing to the hard drives? we're not restoring from a pg_dump. We were in the process of moving the db to a new server. We brought the db down (on host A) and did an rsync of all the db dir's (including tablespace dir's) to host B. Then bad things were done to host A and we want to get back to where we started. So, we stopped the db on host A and rsync'ed the files back from host B to host A. Now when I try and start the db I see all these tx sement errors in the log. We're actually ok if we loose anything that was in the pg_xlog dir. Did you rsync the pg_xlog and pg_clog (i.e. EVERYTHING under data/) or just the base directory? You really need to do an rsync of everything, not most everything. we rsync'ed EVERYTHING (the entire directory and all sub-dirs) OK, on my laptop, in the /var/lib/postgresql/8.3/main there's a bunch of dirs that look like this: basepg_clog pg_subtrans pg_twophase pg_xlog postmaster.pid server.crt global pg_multixact pg_tblspcPG_VERSION postmaster.opts root.crtserver.key Did you rsync the base dir only, or all of the directories above? Cause if you just rsynced base, you need the rest of them, as well. I rsync'd the /var/lib/postgresql/8.3/main dir. However several of the sub- dirs were soft links (pg_xlog, server.cert, root.cert and server.key) so I unfortunately did not get the data from the links' real location since they all pointed to outside of the /var/lib/postgresql/8.3/main tree -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
