[GENERAL] Insert Rewrite rules
I am currently attempting to migrate one of our customers databases to
partitioned tables. This database is used to store firewall logs and
is currently in the range of 600GB (thats 90 days worth).
I am having problems with the rewrite rules though it seems to be skipping over
any rule that has a where statement in it ie
CREATE OR REPLACE RULE firewall_test AS ON INSERT TO firewall DO INSTEAD INSERT
INTO firewall_y2008m04d21 VALUES(NEW."time");
INSERT INTO firewall (time) VALUES('2008-04-21 12:00:00');
INSERT 1029459 1
works but
CREATE OR REPLACE RULE firewall_test AS ON INSERT TO firewall WHERE TRUE DO
INSTEAD INSERT INTO firewall_y2008m04d21 VALUES(NEW."time");
INSERT INTO firewall (time) VALUES('2008-04-21 12:00:00');
ERROR: No inserting to firewall please
doesn't. I have placed a trigger on the table to prevent anything from
inserting into the top level table hence the error.
an example of the full rule we are trying to use that doesn't work is
firewall_y2008m04d21_insert AS
ON INSERT TO firewall
WHERE new."time" >= '2008-04-21 00:00:00'::timestamp without time zone AND
new."time" < '2008-04-22 00:00:00'::timestamp without time zone DO INSTEAD
INSERT INTO firewall_y2008m04d21 ("time", firewall, pri, sent, rcvd, lsent,
lrcvd, duration, src, dst, arg, msg, ref, dstname, srcname, agent, server,
srcclass, dstclass, rule, username, proto, op, result, vpn, type, cache,
direction, content, fwdfor, coninfo, tcpflags, method, action, policy, service,
engine, state, fwid, block, authprofile, summarised, realm, clientmac, account,
count, interface)
VALUES (new."time", new.firewall, new.pri, new.sent, new.rcvd, new.lsent,
new.lrcvd, new.duration, new.src, new.dst, new.arg, new.msg, new.ref,
new.dstname, new.srcname, new.agent, new.server, new.srcclass, new.dstclass,
new.rule, new.username, new.proto, new.op, new.result, new.vpn, new.type,
new.cache, new.direction, new.content, new.fwdfor, new.coninfo, new.tcpflags,
new.method, new.action, new.policy, new.service, new.engine, new.state,
new.fwid, new.block, new.authprofile, new.summarised, new.realm, new.clientmac,
new.account, new.count, new.interface)
There is one of these for each day with only the times changing.
Am I missing something or is this just broken?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL over HTTP/REST interface
hi all, Yahoo China sponsored a small project to build a WebDB interface based on HTTP/JSON/REST etc. and it could be found from here: http://search.cpan.org/~agent/OpenResty-0.1.10/lib/OpenResty.pm any comments are welcome! -laser -- 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] Too many LWLocks taken in query using pg_tgrm & GIN index
Craig Ringer <[EMAIL PROTECTED]> writes: > I've just hit a reproducible error with a query that uses pg_trgm: > ERROR: too many LWLocks taken This is absolutely a bug. Please send a complete test case to pgsql-bugs. 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
[GENERAL] Too many LWLocks taken in query using pg_tgrm & GIN index
Hi I've just hit a reproducible error with a query that uses pg_trgm: ERROR: too many LWLocks taken I'm using PostgreSQL 8.3.1 . The database is the one generated by the script I posted recently for reading .po files. It's tiny, with only 7000 records in the table being queried to produce this error. It's also easily generated using that script. I'm encountering the error with the following query, which seeks to find similar looking messages: SELECT a.message, b.message FROM po_message a, po_message b WHERE a.id <> b.id AND a.message % b.message ORDER BY similarity(a.message,b.message) desc; The query is intended to find similar looking messages as a test to make sure the po reader script isn't inserting multiple slightly different versions of a message from different po files. If I remove the ORDER BY clause the error still occurs. The error does not occur if I remove the '%' operator. The table in question has a GIN index on the `message' column. If I drop that index and replace it with a GIST index, the issue no longer occurs. Dropping the index and recreating it as GIN again causes the problem to reappear, so it wasn't a corrupt index. Here's the query plan for the query without the ORDER BY: EXPLAIN SELECT a.message, b.message FROM po_message a, po_message b WHERE a.id <> b.id AND a.message % b.message; QUERY PLAN - Nested Loop (cost=0.00..8259.37 rows=59436 width=94) Join Filter: (a.id <> b.id) -> Seq Scan on po_message a (cost=0.00..161.10 rows=7710 width=51) -> Index Scan using po_message_trigrm_idx on po_message b (cost=0.00..0.93 rows=8 width=51) Index Cond: (a.message % b.message) Filter: (a.message % b.message) (6 rows) This isn't an issue for me, but I thought I should post it in case it does indicate a bug lurking somewhere, like some kind of lock leak in the tgrm gin index code. Ideas? -- 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] Changed Hosts, Lots of Errors in PostgreSQL - Help Please!
Craig Ringer wrote: BLazeD wrote: [quote]PHP Warning: pg_query(): Query failed: ERROR: operator does not exist: timestamp without time zone > time without time zone That's really odd. I can't imagine why the timestamp > timestamp operator might be absent. Hmm I managed to read that error repeatedly and somehow STILL miss `time' vs `timestamp'. Please disregard my reply, as it's of no use due to that misreading. -- 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] Changed Hosts, Lots of Errors in PostgreSQL - Help Please!
At 11:27p -0400 on Fri, 18 Apr 2008, BLazeD wrote: [quote]PHP Warning: pg_query(): Query failed: ERROR: operator does not exist: timestamp without time zone > time without time zone at character 14\nHINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.\nQUERY: SELECT $1 > $2 ^^^ It's been awhile since I've messed with timestamps et al, but I don't recall direct operators between timestamp and time. In the host change, did you also get a Postgres upgrade? To 8.3 perhaps, that removed a lot of explicit type casts? [other errors] If that's it, I'll bet fixing the first error will fix the rest, or at least point you in the right direction. Kevin -- 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] Changed Hosts, Lots of Errors in PostgreSQL - Help Please!
BLazeD <[EMAIL PROTECTED]> writes: > [quote]PHP Warning: pg_query(): Query failed: ERROR: operator does not > exist: timestamp without time zone > time without time zone at character > 14 Well, it's quite right, there is no such operator. PG 8.3 complains about this, whereas previous versions would have silently converted both operands to text and done a textual comparison ... leading to results that are highly unlikely to be sane at all, for this combination of datatypes. I'd say 8.3 just found a bug in your app for you. > [quote]PHP Warning: pg_query(): Query failed: ERROR: function > pg_catalog.btrim(bigint) does not exist at character 62 Again, this is 8.3 being more picky about implicit casts than prior versions. Why would you think btrim on an integer value would be useful, anyway? If you really want it, insert an explicit cast to 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] In the belly of the beast (MySQLCon)
On Fri, Apr 18, 2008 at 9:04 PM, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > On Fri, 18 Apr 2008 14:59:34 -0400 > Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > > I find it pretty unlikely that Slony would be unable to help you > > upgrade here. Obviously you can upgrade one database at a time. > > > > Also, mostly it's not the database size what's a concern, but rather > > the size of the largest table. > > As I recall (I could be wrong) Slony syncs the whole set as a single > transaction. So if he has his entire database as a set he may > have a problem regardless of the largest or smallest table. I would also > agree that 30 million rows is likely not a problem but he should still > check his velocity. Well, you can sync one table at a time (create a (temporary) set with one able, subscribe it, SYNC, MERGE SET, rince, lather, repeat). :) Regards, Dawid -- 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] In the belly of the beast (MySQLCon)
On Sun, Apr 20, 2008 at 12:48 PM, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > On Sun, 20 Apr 2008 11:32:58 -0600 > "Scott Marlowe" <[EMAIL PROTECTED]> wrote: > > > Exactly. There are several things you can do on the replica that you > > wouldn't do on the master for better performance. Really big numbers > > of WAL segments, no background writer, fsync off as mentioned, WAL on > > a RAID-0 with 10 disks, no battery back on a caching RAID controller, > > and so on. > > You are still limited by how fast you can pull data from the master > over the network to the slave. Which when dealing with 500GB+ is a > significant limitation. Agreed. Hence the part of my post about using bonded gigabit ethernet to boost that speed. Admitted, there's only so much you can do there. -- 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] In the belly of the beast (MySQLCon)
On Sun, 20 Apr 2008 11:32:58 -0600 "Scott Marlowe" <[EMAIL PROTECTED]> wrote: > Exactly. There are several things you can do on the replica that you > wouldn't do on the master for better performance. Really big numbers > of WAL segments, no background writer, fsync off as mentioned, WAL on > a RAID-0 with 10 disks, no battery back on a caching RAID controller, > and so on. You are still limited by how fast you can pull data from the master over the network to the slave. Which when dealing with 500GB+ is a significant limitation. Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] JDBC addBatch more efficient?
Just checking if the JDBC library's batch processing code is more efficient with respect to the postgresql back end or not. Does it really batch the requests and submit them once over the link, or does it just send them to the database to be processed one at a time? Thanks, David -- 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] Changed Hosts, Lots of Errors in PostgreSQL - Help Please!
On Friday 18 April 2008 8:27 pm, BLazeD wrote:
> Hi All
>
> I recently changed hosts for my PHP/PostgreSQL site and have been seeing
> alot of errors in the errors logs and also some on the site.
>
> [quote]PHP Warning: pg_query(): Query failed: ERROR: operator does not
> exist: timestamp without time zone > time without time zone at character
> 14\nHINT: No operator matches the given name and argument type(s). You
> might need to add explicit type casts.\nQUERY: SELECT $1 > $2
> \nCONTEXT: PL/pgSQL function "f_touch_user" line 29 at IF in
> //include/database.php on line 40[/quote]
>
> [quote]PHP Warning: pg_fetch_array() expects parameter 1 to be resource,
> boolean given in //include/common.php on line 402[/quote]
>
> [quote]PHP Warning: pg_query(): Query failed: ERROR: function
> pg_catalog.btrim(bigint) does not exist at character 62\nHINT: No function
> matches the given name and argument types. You might need to add explicit
> type casts. in //include/database.php on line 40[/quote]
>
> [quote]PHP Notice: Undefined index: HTTP_REFERER in
> /include/common.php on line 483[/quote]
>
> Does anyone know what might be causing this? Here are the relevant lines
> from the code:
>
> [B]database.php on line 40[/B]
>
> [quote]$l_hResult = pg_query($this->m_pHandle, $i_sQuery);[/quote]
>
> [B]common.php on line 402[/B]
>
> [quote]while ($l_asRow = pg_fetch_array($l_hResult)) {[/quote]
>
> [B]common.php on line 483[/B]
>
> [quote]$l_sReferer = isset($_POST['referer'])?
> trim($_POST['referer'])
>
> : base64_encode($_SERVER['HTTP_REFERER']);[/quote]
>
> Thank you!
Did the Postgres versions change from one host to another?
--
Adrian Klaver
[EMAIL PROTECTED]
--
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] Changed Hosts, Lots of Errors in PostgreSQL - Help Please!
BLazeD wrote: [quote]PHP Warning: pg_query(): Query failed: ERROR: operator does not exist: timestamp without time zone > time without time zone That's really odd. I can't imagine why the timestamp > timestamp operator might be absent. What's the output of the command "select version()" on your new host? If you connect to your database with the psql command line tool and run the command: \do > does an entry with both operand types `timestamp without time zone' appear? If you don't have direct access using psql, you can wrap the following query up in a bit of PHP and see what the result of it is instead: select * from pg_catalog.pg_operator where oprcode = 'timestamp_gt'::regproc; at character 14\nHINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.\nQUERY: SELECT $1 > $2 \nCONTEXT: PL/pgSQL function "f_touch_user" line 29 at IF in //include/database.php on line 40[/quote] OK, so it's inside PL/PgSQL. Do you have the same problem if you execute a similar query manually, like: select current_timestamp :: timestamp without time zone > current_timestamp :: timestamp without time zone; ? If not, try reloading your stored procedures and see if you still have problems. If you still have issues, wrap a simple test statement up in a PL/PgSQL stored procedure and see if it executes correctly, eg: CREATE OR REPLACE FUNCTION testop () RETURNS boolean AS $$ BEGIN return current_timestamp :: timestamp without time zone > current_timestamp :: timestamp without time zone; END; $$ LANGUAGE 'plpgsql'; ... though I cannot imagine why it might work as a standalone statement but not in a stored procedure. If you want more help I suggest posting the actual SQL statements that are causing problems. If possible get them from the server logs after enabling statement logging, or from the pg interface in PHP if it has any statement logging features. -- 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] How to tell if 64 bit vs 32 bit engine?
Zoltan Boszormenyi wrote: > select version(); > > It will tell you the compiler version and arch as well. You can deduce > from there. That approach is not reliable. I often build and run a 32-bit build of PostgreSQL on a machine that claims to be something like x86_64-unknown-linux-gnu. -- 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] In the belly of the beast (MySQLCon)
On Sun, Apr 20, 2008 at 11:12 AM, Scott Ribe <[EMAIL PROTECTED]> wrote: > > I am going to play with this and see where it breaks, but it's going to be > > an enormous time investment to babysit it. > > One thing to remember, since you've already got backup in place and this > replica would just be for upgrading, not a hot spare, turn off fsync during > the initial subscription. My data set is quite a bit smaller, and I've > gotten into the habit of turning off fsync during the initial post-upgrade > load, to shorten my downtime. Exactly. There are several things you can do on the replica that you wouldn't do on the master for better performance. Really big numbers of WAL segments, no background writer, fsync off as mentioned, WAL on a RAID-0 with 10 disks, no battery back on a caching RAID controller, and so on. You also might look into cheap but useful things like bonded gigabit networking between the two servers with a dedicated switch / rolled cable. I wonder if there's a comprehensive list somewhere... What I keep dreaming of is a process that lets slony use pg_bulkloader or something like it to do the initial load... -- 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] In the belly of the beast (MySQLCon)
> I am going to play with this and see where it breaks, but it's going to be > an enormous time investment to babysit it. One thing to remember, since you've already got backup in place and this replica would just be for upgrading, not a hot spare, turn off fsync during the initial subscription. My data set is quite a bit smaller, and I've gotten into the habit of turning off fsync during the initial post-upgrade load, to shorten my downtime. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice -- 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] good experience with performance in 8.2 for multi column indexes
No, both negative. Michael Tom Lane wrote: Michael Enke <[EMAIL PROTECTED]> writes: For my setup, in 8.1 a delete query which deletes 20 entries depending on rows in another table runs about 7h, in 8.2 (and later) it runs 9s! If those rowcount estimates are even close to accurate, there's no reason for the hash plan to be so much faster than the indexscan. I think the real issue is somewhere else. Do you have any triggers or foreign keys on this table? regards, tom lane -- Wincor Nixdorf International GmbH Sitz der Gesellschaft: Paderborn Registergericht Paderborn HRB 3507 Geschäftsführer: Eckard Heidloff (Vorsitzender), Stefan Auerbach, Dr. Jürgen Wunram Vorsitzender des Aufsichtsrats: Karl-Heinz Stiller Steuernummer: 339/5884/0020 - Ust-ID Nr.: DE812927716 - WEEE-Reg.-Nr. DE44477193 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese E-Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser E-Mail ist nicht gestattet. This e-mail may contain confidential information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden. -- 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] Which Python library - psycopg2 or pygresql?
On 18 Apr, 14:12, [EMAIL PROTECTED] (Karsten Hilbert) wrote: > > If one wants to operate on one/a range of row(s) but the > code fetches "all" rows (for various values of all) then I'd > suspect there's something missing in the SQL statement, say, > a LIMIT or appropriate WHERE conditions - regardless of > whether a cursor is used or not. But if you want to process all of the rows, and you don't want the client to suck them all down at once, then you need to use the database system's cursor support. > If you refer to whether server-side cursors are used one > must explicitly request them from psycopg2 by using the > "name" argument to the connection.Cursor() call. Combine > that with a Python generator and one should end up with > truly on-demand single-row fetching. As I noted, the problem is arguably shared between the database system (because cursors don't work with certain statements that you might use, and there's no way of finding out without trying) and the database adapter (because it doesn't try to support the behaviour implied by the DB-API). Inventing names for cursors, although tedious, is the easy part in all this. > Unfortunately, I am not entirely sure how and when psycopg2 > uses (database) cursors when no name argument is supplied. It doesn't. > IMO the cursor concept of the DB-API is broken anyhow - > everything is forced to go through a (DB-API) cursor no > matter whether a database-side cursor would be wanted or not > and there's no provision for controlling the latter via the > API itself. Well, the DB-API doesn't seem to be moving in any real direction these days, anyway. I've wanted and even proposed code for a single parameter standard, and the progress on that matter has been glacial: it's too controversial to do what ODBC and JDBC have been doing for years, apparently. Still, I don't really see that doing the equivalent of a cursor.fetchall for something like cursor.fetchone is appropriate when "all" might be millions of rows, but that's just my view. Paul -- 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] How do I measure user disk usage on Postgresql table?
"Richard Huxton" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Dave wrote: >> Lets say I have a Postgresql table where I store uploaded data for >> all users, identified by user_id column. Is it possible to run a >> query to measure the storage a user is consuming? > > Not really. You could get the size of the whole table with > pg_relation_size() and then estimate a user's share by seeing what % of > rows have their user-id. > > If it's the sort of thing you'll be checking constantly, then I'd keep a > summary table up-to-date using triggers instead. > > Oh - if you're going to be asking questions regularly then you'll probably > get more answers if you have a valid email address. > > -- > Richard Huxton > Archonet Ltd > > -- Richard, Thanks for the answer. So what is the proper way of managing user quota on database (apart from having separate table for each)? > Oh - if you're going to be asking questions regularly then you'll probably > get more answers if you have a valid email address. Isn't that asking for spam (and I know the answer to that)? Why would it matter for someone to answer my post based on my email address, if the discussion is going over the groups? One might just as well use: [EMAIL PROTECTED] At least I'm forthcoming by making it obvious that my email address is not real. Just a thought. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Changed Hosts, Lots of Errors in PostgreSQL - Help Please!
Hi All
I recently changed hosts for my PHP/PostgreSQL site and have been seeing
alot of errors in the errors logs and also some on the site.
[quote]PHP Warning: pg_query(): Query failed: ERROR: operator does not
exist: timestamp without time zone > time without time zone at character
14\nHINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.\nQUERY: SELECT $1 > $2
\nCONTEXT: PL/pgSQL function "f_touch_user" line 29 at IF in
//include/database.php on line 40[/quote]
[quote]PHP Warning: pg_fetch_array() expects parameter 1 to be resource,
boolean given in //include/common.php on line 402[/quote]
[quote]PHP Warning: pg_query(): Query failed: ERROR: function
pg_catalog.btrim(bigint) does not exist at character 62\nHINT: No function
matches the given name and argument types. You might need to add explicit
type casts. in //include/database.php on line 40[/quote]
[quote]PHP Notice: Undefined index: HTTP_REFERER in
/include/common.php on line 483[/quote]
Does anyone know what might be causing this? Here are the relevant lines
from the code:
[B]database.php on line 40[/B]
[quote]$l_hResult = pg_query($this->m_pHandle, $i_sQuery);[/quote]
[B]common.php on line 402[/B]
[quote]while ($l_asRow = pg_fetch_array($l_hResult)) {[/quote]
[B]common.php on line 483[/B]
[quote]$l_sReferer = isset($_POST['referer'])? trim($_POST['referer'])
: base64_encode($_SERVER['HTTP_REFERER']);[/quote]
Thank you!
--
View this message in context:
http://www.nabble.com/Changed-Hosts%2C-Lots-of-Errors-in-PostgreSQL---Help-Please%21-tp16769300p16769300.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
