Hi all, <PREMISE> you might forgive me if I don't use Trac, but I'd like to explain and discuss what I'm doing with PGSQL schema.sql for Habari before submitting (if my contrib will be accepted code wise) anything.
Not to mention that in these very days things are getting moved on Github. If so, I would clone and request for pull once there, since I have an active account on Github and it seems pointless to get a Trac account now. BUT: since this very patch isn't going (at least theoretically) to have any bad effect on Habari , I'm attaching it to this very email for now. For you to test. Forgive me on this last thing but I haven't set up a test env just yet. I'll do it after sending this patch. <little INTRO and WHY> I'm cga. I'm a Linux SysAdmin and I'm all for "best practices" as much as I can. Not to mention I'm really paranoid and analytical when it concerns coding/installing/configuring/deploying. Especially for things I care/use/ administer. I basically started this little contribution because I really love my stack: debian + postgresql + nginx and I'd like to use it for all my needs. Habari included. Ref: http://drunkenmonkey.org/irc/habari/2011-06-19#T17-04-24 I think it'sfair enough for a small intro. <CONTRIB AND REASONING> As you can read in the chat log I linked above , I'm not expert in neither PostgreSQL nor SQL but I like to contribute for as much as I can. Especially if this can be the only way to keep Habari's support for PostgreSQL alive. This first little but important contribution it is all about the "CREATE SEQUENCE" used in PGSQL actual schema.sql. All the reasoning I do it's to be documented w/ links to official docs and (attacched) chat logs from freenode.#postgresql , where (beside docs and wiki) I learn and ask my stuff. And to my great pleasure I always find very competent and very helpful people. Please forgive my English errors, I'm Italian. Please *really* forgive my verbosity but I'm sure you'd understand that people who didn't follow in freenode.#habari don't know all the reasoning from the room. If I'm to be accepted I promise I'll be as concise as possible in next communications. Here we go: I noticed that you use a lot of serialization in schema.sql , w/o questioning why (which I'm not in a position to, anyway) I noticed that you (being whoever did that schema.sql) do it wrong. Why? Because: CREATE SEQUENCE done in the present form from schema.sql: >CREATE SEQUENCE {$prefix}posts_pkey_seq; >CREATE TABLE {$prefix}posts ( > id BIGINT NOT NULL DEFAULT nextval('{$prefix}posts_pkey_seq'), >..... >..... > PRIMARY KEY (id), >..... >); > 1 - It's *almost* correct, but no cigar. It basically miss the most crucial part of the query to make the SEQUENCE itself have any sense *and* work properly: > ALTER SEQUENCE {$prefix}posts_pkey_seq OWNED BY{$prefix}posts.id; w/o this ALTER TABLE the SEQUENCE is to behave weird more sooner than later, resulting in duplicate numbers (see chat log; using BIGINT won't save you from this, more on this later) and errors that are more than diffcult to find when they happen. 2 - it determines in a recursive way the name of the SEUQNECE itself , which is wrong because you cannot be 100% sure that will actually work as intended. (see chat log). 3 - it uses BIGINT. My reasoning is that whoever created this schema.sql didn't wan't to finish the SEQUENCE too soon. Good idea , bad implementation. I strongly believe that BIGINT is not needed indeed, and that it's just a waste of cylces and space. Not to mention that BIGINT behaves differently on 32bit and 64bit, causing slowness (and perhaps some more side effects) on 32bit. (this is chat logs from the office, if you need them I'll attach them tomorrow). And as explained just above, it is not going to be used as a BIGINT anyway because of the wrong query CREATE SEQUENCE. But the main point why it is not needed it's because who's to write a post every second for # quintillions years? I think that a post every second for 68yrs is more than enough (INTEGER/SERIAL). (see chat log too). 4 - when all the above are to be combined w/ the use of NOT NULL DEFAULT nextval()" (and I mean all of them are compulsory clauses, see chat log), you can save a lot of hassles, code and cycles with just the improvement I'm submitting for every CREATE SEQUENCE query, leaving intact the final result for what it concerns Habari's functioning *and* improves things a whole lotta love: >CREATE TABLE {$prefix}posts ( > id SERIAL, >..... >..... > PRIMARY KEY (id), >..... >); <IMPROVEMENTS ACHIEVED> Improvements and optimizations achieved with this little patch: (see Ref. [2]) 0 - it will leave intact the need of Habari to use the numerical field for internal functioning. 1 - (id) will be unique and auto incremental by properties inherited from query. 2 - You won't skip a number in a SEQUENCE. 3 - It won't fail because of wrong creation query. 4 - It won't determine an unexpected SEQUENCE name/relation to the column you want it to. 5 - OWNED BY is to be used implicitly , this will drop anything related to the elected talbe and/or column. This sounds harsh, but what will you do w/ data that isn't associated w/ anything anymore? <FINAL THOUGHTS> 1 - For the impact on Habari's code (which I didn't read because I don't do php and couldn't afford to get it wrong) on insert the next value of the sequence into the serial column please read Ref. [2] just below the PostgreSQL 7.3 Note. 2 - This new schema.sql would work (in theory) for new installations but I don't know how to implement and test a system/schema/pgsql/upgrades/post/*.sql yet. Help and advice from you is very much apprecciated. 3 - Let me know if anyone is going to test this before I do. If you want. Anyway I'm going to test it when my test env will be up and running. 4 - I'm already planning the next batch of fixes but that can wait for now. I anticipate that it will be about optimizing queries and about keyvil issues as much as possible and w/o breaking Habari Ref. [3]. My pleasure to meet you. <REFERENCES> Ref. chat log attacched. Ref [1]. http://www.postgresql.org/docs/8.4/interactive/sql- createsequence.html Ref [2]. http://www.postgresql.org/docs/8.4/interactive/datatype- numeric.html#DATATYPE-SERIAL Ref. [3] http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-i-7327 http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-ii-7345 http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-iii-7365 -- knowledge has no owner, only means to reveal itself
[Tuesday 21 June 2011] [21:58:12] <cga> hi, i'm trying to help for what i can habari project. my contribution is to keep alive/optimize postgresql support. i'm not sql/pgsql expert but i'm willing to help since i don't want to use wordpress and mysql. considering that i'm just planning and reading on pgsql wiki and docs, and that CREATE SEQUENCE does not use cache (so it's 1 and positive numbers) can i optimize this http://pgsql.privatepaste.com/638ae3a179 , like trying to? [Tuesday 21 June 2011] [21:59:28] <RhodiumToad> those are equivalent, yes (the first one is of course impossible due to the circular reference) [Tuesday 21 June 2011] [21:59:49] <cga> RhodiumToad: so the second is correct? great! [Tuesday 21 June 2011] [22:00:10] <RhodiumToad> you should avoid assuming you know the sequence name, of course - use insert ... returning or pg_get_serial_sequence [Tuesday 21 June 2011] [22:01:37] <cga> RhodiumToad: i see. but sequence it's a new concept to me. i 'm just trying to optimize existing "schema.sql" from habari previous maintainer [Tuesday 21 June 2011] [22:02:36] <cga> RhodiumToad: can you help me understand what you meant with avoiding ? what are the conseuqences? [Tuesday 21 June 2011] [22:03:15] <RhodiumToad> when you use create table foo (id bigserial); postgres creates a sequence which is usually named foo_id_seq [Tuesday 21 June 2011] [22:03:44] <RhodiumToad> but you should never assume that's what the name is, instead the function pg_get_serial_sequence('foo','id') will return the name [Tuesday 21 June 2011] [22:04:20] <RhodiumToad> if you assume you know the name, then things will break if you ever rename the table, the column, or the sequence [Tuesday 21 June 2011] [22:04:39] <cga> great RhodiumToad =) thank you [Tuesday 21 June 2011] [22:04:51] <RhodiumToad> also if the table and column names are long enough, the sequence name may be chosen arbitrarily [Tuesday 21 June 2011] [22:05:20] <cga> oh [Tuesday 21 June 2011] [22:05:40] <cga> you mean that based on length it could not go cache+1 ? [Tuesday 21 June 2011] [22:05:51] <RhodiumToad> huh? [Tuesday 21 June 2011] [22:07:18] <cga> [22:04] <RhodiumToad> also if the table and column names are long enough, the sequence name may be chosen arbitrarily < this [Tuesday 21 June 2011] [22:07:38] <RhodiumToad> create table thisisareallyverylongtablename (thisisareallyverylongcolumnname bigserial); [Tuesday 21 June 2011] [22:07:41] <cga> so if i have aaaaaaaaaaaaaa.ccccccccc then sequence could act arvbitrarily ? [Tuesday 21 June 2011] [22:07:47] <cga> ok that's it [Tuesday 21 June 2011] [22:07:53] <RhodiumToad> the sequence in that case will be "thisisareallyverylongtablenam_thisisareallyverylongcolumnna_seq" [Tuesday 21 June 2011] [22:08:28] <RhodiumToad> and if you then create a similarly-named table, you might get sequences name blah_seq1 or _seq2 etc. due to name collisions [Tuesday 21 June 2011] [22:09:46] <RhodiumToad> oh, and also if you have both create table foo (bar_baz bigserial); and create table foo_bar (baz bigserial); [Tuesday 21 June 2011] [22:10:03] <cga> you kind of lost me here sorry [Tuesday 21 June 2011] [22:10:15] <cga> let me try [Tuesday 21 June 2011] [22:10:33] <RhodiumToad> both of those will try and create foo_bar_baz_seq so one of them will end up named _seq1 instead [Tuesday 21 June 2011] [22:10:54] <RhodiumToad> the name chosen for the sequence will always be unique [Tuesday 21 June 2011] [22:11:01] <cga> but? [Tuesday 21 June 2011] [22:11:28] <RhodiumToad> because pg will change the name to make it unique, you can't assume you know what the name is [Tuesday 21 June 2011] [22:11:35] <cga> for what i understand if seq is always unique it is better, right? [Tuesday 21 June 2011] [22:11:45] <cga> ok now i get the point [Tuesday 21 June 2011] [22:11:50] <RhodiumToad> we're talking about the NAME of the sequence, not the value [Tuesday 21 June 2011] [22:11:59] <cga> thanks a lot RhodiumToad [Tuesday 21 June 2011] [22:12:12] <cga> one more question they are asking me and i propose to you [Tuesday 21 June 2011] [22:12:23] <cga> "does id remain an integer for read purposes?" [Tuesday 21 June 2011] [22:12:36] <cga> because habari uses that a lot for internal references [Tuesday 21 June 2011] [22:12:37] <RhodiumToad> if you use bigserial then it's a bigint [Tuesday 21 June 2011] [22:12:43] <RhodiumToad> if you use serial then it's an integer [Tuesday 21 June 2011] [22:12:44] <cga> cool =) [Tuesday 21 June 2011] [22:15:53] <RhodiumToad> status? [Tuesday 21 June 2011] [22:15:57] <cga> RhodiumToad: one doubt i have: BISERIAL also means UNIQUE , right? [Tuesday 21 June 2011] [22:16:06] <RhodiumToad> cga: no [Tuesday 21 June 2011] [22:16:08] <cga> oh [Tuesday 21 June 2011] [22:16:15] * cga reads about it on docs [Tuesday 21 June 2011] [22:16:18] <RhodiumToad> cga: if you want UNIQUE or PRIMARY KEY you have to say so [Tuesday 21 June 2011] [22:16:44] <RhodiumToad> cga: serial/bigserial is basically just short for integer/bigint default nextval('whatever') [Tuesday 21 June 2011] [22:16:58] <cga> i see [Tuesday 21 June 2011] [22:17:05] <cga> should i specify that on same line? [Tuesday 21 June 2011] [22:17:19] <RhodiumToad> cga: you almost always want PRIMARY KEY, since the only reason to generate a surrogate key column is to use it as a key [Tuesday 21 June 2011] [22:17:40] <cga> like id BIGSERIAL PRIMARY KEY; ? [Tuesday 21 June 2011] [22:18:32] <cga> anyway going to read the docs. so i can learn and leave you to your things. (just to disturb you later with better questions :P) [Tuesday 21 June 2011] [22:19:44] <bgrace> RhodiumToad, I found on the internet that 64 bit does not have the uuid-package [Tuesday 21 June 2011] [22:20:44] <cga> RhodiumToad: found the notes http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-SERIAL [Tuesday 21 June 2011] [22:27:52] <cga> is NOT NULL UNIQUE possible? or should i define them separately? [Tuesday 21 June 2011] [22:28:33] <cga> mmm i guess not :F [Tuesday 21 June 2011] [22:42:18] <cga> RhodiumToad: there are some constraints in habari that i didn't consider , they use *a lot* ${prefix}foo ${prefix}bar ${prefix}baz in all of their CREATE TALBE and CREATE SEQUENCE because they say that ${prefix} == tableprefix_ in database. they are doing this because this will allow people using an hosting with a single db to create different blogs/talbes/tableprefix . can i show you the schema.sql in a paste and you tell me if i'm wasting time? please and [Tuesday 21 June 2011] [22:42:18] <cga> thank you [Tuesday 21 June 2011] [22:43:01] <RhodiumToad> in pg one would normally use schemas rather than the prefix stuff [Tuesday 21 June 2011] [22:43:13] <peerce> cga: thats not uncommon. drupal and other portable packages do much the same [Tuesday 21 June 2011] [22:43:44] <cga> hi peerce, I see [Tuesday 21 June 2011] [22:44:17] <cga> RhodiumToad: i get it but don't understand it. [Tuesday 21 June 2011] [22:46:01] <cga> but there's another consideration: habari supporting pgsql mysql and sqlite tries to keep "schema.sql" as much agnostic as they can, so my task is to get it better/optimize it but not chaging things too much. (or at least to the point where it breaks things). [Tuesday 21 June 2011] [22:46:34] <cga> and of course since i'm not expert i'm trying to reason it beforehand [Tuesday 21 June 2011] [22:48:19] <peerce> cga; there's little point in optimizing things that aren't a problem. [Tuesday 21 June 2011] [22:49:22] <cga> peerce: true... i just had that epiphany too... by reading the code better..... [Tuesday 21 June 2011] [22:49:36] <cga> thanks =) [Tuesday 21 June 2011] [22:50:20] <cga> peerce: one thing that i'd really like to fix is "PKEY (id)" [Tuesday 21 June 2011] [22:50:30] <cga> i read keyvil already and i agree [Tuesday 21 June 2011] [22:50:43] <cga> but i'm in doubt what else to put there [Tuesday 21 June 2011] [22:51:34] <cga> i had a chat yesterday, and they said that tehre are only 2 maybe 3 columns that really need to be unique: id slug (perhaps guid) [Tuesday 21 June 2011] [22:51:48] <cga> so i tought it could be good to make them as PKEY [Tuesday 21 June 2011] [22:51:53] <cga> what do you reckon? [Tuesday 21 June 2011] [22:52:13] <RhodiumToad> if you're working with an existing app then don't try and get too radical with schema changes. [Tuesday 21 June 2011] [22:52:57] <RhodiumToad> the time to think about the stuff in the keyvil article is when you're sitting down to design something new, not when hacking on existing code [Tuesday 21 June 2011] [22:53:34] <cga> points taken [Tuesday 21 June 2011] [22:53:50] <cga> but they still are in time. it's a very young blog engine [Tuesday 21 June 2011] [22:54:02] <cga> and they said that i can change and test locally [Tuesday 21 June 2011] [22:54:16] <cga> they'll take care of porting if my changes are good enough [Tuesday 21 June 2011] [22:54:40] <cga> and of course: if they work w/o braking too much [Tuesday 21 June 2011] [22:55:05] <cga> i think that for now i'll just optimize sequences and submit [Tuesday 21 June 2011] [22:55:10] <cga> it's almost bed time here [Tuesday 21 June 2011] [22:57:48] <RhodiumToad> no [Tuesday 21 June 2011] [22:58:13] <RhodiumToad> things like sort tapes create multiple physical files [Tuesday 21 June 2011] [22:59:16] <RhodiumToad> a really nasty sort will often result in a lot of temp files [Tuesday 21 June 2011] [22:59:38] <RhodiumToad> what was the query like? [Tuesday 21 June 2011] [23:01:13] <intgr> RhodiumToad: This is the query: http://dpaste.com/557116/ [Tuesday 21 June 2011] [23:01:32] <RhodiumToad> pg_lizard: work_mem is allocated from normal process memory [Tuesday 21 June 2011] [23:02:19] <RhodiumToad> what's the explain look like? [Tuesday 21 June 2011] [23:02:36] <RhodiumToad> without ANALYZE if it takes too long to execute [Tuesday 21 June 2011] [23:02:49] <pg_lizard> Thanks RhodiumToad [Tuesday 21 June 2011] [23:03:59] <RhodiumToad> htaccess: -P pager=off [Tuesday 21 June 2011] [23:04:13] <intgr> RhodiumToad: Right now this query is fast, 2 seconds: http://explain.depesz.com/s/BCb [Tuesday 21 June 2011] [23:04:16] <RhodiumToad> htaccess: or redirect either stdin or stdout to be not a terminal [Tuesday 21 June 2011] [23:04:38] <htaccess> RhodiumToad: thanks! i had \pset pager off in ~/.psqlrc but that wasent working [Tuesday 21 June 2011] [23:05:51] <RhodiumToad> htaccess: .psqlrc isn't read when doing psql -c [Tuesday 21 June 2011] [23:06:39] <RhodiumToad> intgr: no... sort shouldn't detoast fields that aren't in the sort key [Tuesday 21 June 2011] [23:06:48] <RhodiumToad> intgr: though I'd have to check that [Tuesday 21 June 2011] [23:07:06] <intgr> RhodiumToad: Oh that's cool, hmm... [Tuesday 21 June 2011] [23:07:15] <htaccess> RhodiumToad: yea, i saw that in the docs [Tuesday 21 June 2011] [23:07:59] <RhodiumToad> intgr: without catching it when it's slow this one isn't going to be easy to pin down [Tuesday 21 June 2011] [23:08:04] <intgr> RhodiumToad: If it's any help, this query is being ran every 15 minutes by a cron job. I've noticed similar problems 3 times in the past. [Tuesday 21 June 2011] [23:08:13] <RhodiumToad> intgr: contrib/auto_explain would help if it actually completed when it was slow [Tuesday 21 June 2011] [23:08:55] <intgr> RhodiumToad: Ah another thing, the query didn't want to terminate in response to pg_cancel_backend/pg_terminate_backend [Tuesday 21 June 2011] [23:10:23] <sternocera> RhodiumToad: As a matter of interest, do you have an opinion on the state of PG log parsers? [Tuesday 21 June 2011] [23:10:36] <RhodiumToad> no [Tuesday 21 June 2011] [23:11:18] <sternocera> RhodiumToad: You've never found it useful to use one? [Tuesday 21 June 2011] [23:11:24] <intgr> RhodiumToad: Would statement_timeout have a better chance at killing it than pg_cancel_backend? [Tuesday 21 June 2011] [23:12:18] <RhodiumToad> intgr: no difference [Tuesday 21 June 2011] [23:17:27] <RhodiumToad> last update to the control file [Tuesday 21 June 2011] [23:17:37] <RhodiumToad> the control file is updated at least every checkpoint [Tuesday 21 June 2011] [23:19:27] <RhodiumToad> they may actually be skipped if nothing happened since the last one [Tuesday 21 June 2011] [23:20:21] <RhodiumToad> if a checkpoint is skipped due to inactivity then not even the control file is updated [Tuesday 21 June 2011] [23:22:01] <gurjeet> After what RhodiumToad said, I'd say yes. [Tuesday 21 June 2011] [23:22:10] <justatheory> RhodiumToad: wrongnes? [Tuesday 21 June 2011] [23:24:00] <RhodiumToad> as long as it takes to run the trigger. [Tuesday 21 June 2011] [23:24:23] <RhodiumToad> which depends on what the trigger does. [Tuesday 21 June 2011] [23:24:29] <gurjeet> pg_lizard: Postgres keeps an LRU list of dirty buffers, so no, scanning for dirty data is not expensive (This is my theory, and RhodiumToad may correct me :) [Tuesday 21 June 2011] [23:24:59] <RhodiumToad> gurjeet: it's even simpler than that: a checkpoint is skipped if the WAL pointer has not advanced since the last one. [Tuesday 21 June 2011] [23:25:42] <pg_lizard> Thanks RhodiumToad.. perhaps the decrease in load is due to different reasons and not due to checkpoint deferral [Tuesday 21 June 2011] [23:25:59] <RhodiumToad> gurjeet: and buffers aren't on an LRU list these days (too expensive to maintain) [Tuesday 21 June 2011] [23:26:13] <gurjeet> RhodiumToad: Ah.. so the same optimization is used here that is used for pg_switch_xlog() ... thanks.. [Tuesday 21 June 2011] [23:28:23] <RhodiumToad> gurjeet: checkpoint has to scan all the buffer headers, yes [Tuesday 21 June 2011] [23:29:06] <RhodiumToad> it controls how often checkpoints happen in low-load periods. [Tuesday 21 June 2011] [23:29:21] <RhodiumToad> no, checkpoint_segments controls the recovery time [Tuesday 21 June 2011] [23:29:51] <RhodiumToad> checkpoint_timeout has a small effect on recovery time if you crash during a low-load period. [Tuesday 21 June 2011] [23:30:33] <pg_lizard> ah got it RhodiumToad. Thanks. [Tuesday 21 June 2011] [23:32:39] <RhodiumToad> nonsense [Tuesday 21 June 2011] [23:33:14] <RhodiumToad> a checkpoint_timeout that low will give you problems in higher-load conditions [Tuesday 21 June 2011] [23:33:37] <pg_lizard> hmm ..okay RhodiumToad.. hehe it tough to know the real balance.:) [Tuesday 21 June 2011] [23:35:42] <cga> RhodiumToad: you said that pgsql uses schema instead of ${prefix} , where can i read about that? [Tuesday 21 June 2011] [23:36:21] <RhodiumToad> ??schema [Tuesday 21 June 2011] [23:36:25] <cga> ohps... [Tuesday 21 June 2011] [23:36:27] <cga> thank you [Tuesday 21 June 2011] [23:37:20] <cga> peerce: in your opinion, is it better to leave it alone or to implement it with schema? [Tuesday 21 June 2011] [23:38:05] <RhodiumToad> jonez: isn't it in the -contrib package? [Tuesday 21 June 2011] [23:38:13] <jonez> cga, is your app ever going to run on something other than pgsql? [Tuesday 21 June 2011] [23:38:32] <peerce> cga; doesn't that app have to work with mysql and sqlite interchangably? [Tuesday 21 June 2011] [23:38:33] <cga> hi jonez, it does currently support mysql and sqlite as well [Tuesday 21 June 2011] [23:38:35] <jonez> RhodiumToad, hopefully.. but I don't know the command that does the same thing as 'rpm -ql' so I can find out for sure. [Tuesday 21 June 2011] [23:38:39] <cga> peerce: yes [Tuesday 21 June 2011] [23:38:53] * RhodiumToad can't help with that. [Tuesday 21 June 2011] [23:38:54] <jonez> cga, if neither of those have schema support, then you should leave things as they are (with "prefix") for compatiblity [Tuesday 21 June 2011] [23:39:27] * cga leaves the "it's not broken , don't fix it" for kiss sake [Tuesday 21 June 2011] [23:39:41] <cga> ok thnks =) [Tuesday 21 June 2011] [23:40:08] <RhodiumToad> jonez: there you go then. [Tuesday 21 June 2011] [23:40:35] <RhodiumToad> jonez: just run the ltree.sql file in your database, and Robert is your parental sibling [Tuesday 21 June 2011] [23:43:06] <jonez> RhodiumToad, that's a good thing, right? :) [Tuesday 21 June 2011] [23:43:43] <cga> peerce: one last thing: when replacing the SEQUENCE things we where talking about before, should i "translate" BIGINT to BIGSERIAL (ok) and leave what's INTEGER to SERIAL or also this is a good idea to change it to BIGSERIAL ? i thnk if that they used INTEGER and BIGINT distinctively they had their reason, but since SERIAL / BIGSERIAL is already smaller than INTGER / BIGINT by an order of magnitude, woudln;'t be better to have sequences as large as possible? ( [Tuesday 21 June 2011] [23:43:43] <cga> minding that they have cache =1 by default?) [Tuesday 21 June 2011] [23:44:21] <RhodiumToad> jonez: "Bob's yer uncle" [Tuesday 21 June 2011] [23:44:51] <RhodiumToad> cga: um [Tuesday 21 June 2011] [23:44:58] <RhodiumToad> cga: I think you're very confused [Tuesday 21 June 2011] [23:45:01] <andres> cga: uhm. Why should bigint be smaller by a manitude in comparison to bigserial? [Tuesday 21 June 2011] [23:45:42] <cga> andres: other way around. BIGINT -## and ## while BIGSERIAL is only ### (positive numbers) am i wrong? [Tuesday 21 June 2011] [23:45:53] <RhodiumToad> cga: you're wrong [Tuesday 21 June 2011] [23:46:02] * cga is ashamed :P [Tuesday 21 June 2011] [23:46:04] <cga> ohps... [Tuesday 21 June 2011] [23:46:17] <RhodiumToad> cga: there isn't any storage saved by using serial vs integer [Tuesday 21 June 2011] [23:46:40] <andres> cga: and which practical application has problems with bigints range? [Tuesday 21 June 2011] [23:46:52] Join merlinm has joined this channel (d0568381@gateway/web/freenode/ip.208.86.131.129). [Tuesday 21 June 2011] [23:48:02] <cga> andres: i think none. but in this context {BIG}SERIAL is used in CREATE SEQUENCE not to save space (which i discovered is a wrong concept :P) [Tuesday 21 June 2011] [23:48:45] <merlinm> cga: what's your problem? (curious) [Tuesday 21 June 2011] [23:49:44] <merlinm> yeah -- 2^63 is a pretty big number [Tuesday 21 June 2011] [23:49:53] <RhodiumToad> set a large enough increment... [Tuesday 21 June 2011] [23:49:54] <cga> merlinm: basically: 1) as you can read i'm not sql pgsql expert 2) trying to help w/ habari (blog engine) with schema.sql for pgsql. 3) tonight i leanred about CREATE SEQUENCE and optimizing just that for now. [Tuesday 21 June 2011] [23:50:18] <andres> RhodiumToad: + a ridiculous cache value because caching always makes things faster !!!! [Tuesday 21 June 2011] [23:50:31] <RhodiumToad> snerk [Tuesday 21 June 2011] [23:51:30] <cga> RhodiumToad: so i better do BIGINT => BIGSERIAL and INTEGER => SERIAL in those CREATE SEQUENCE ? [Tuesday 21 June 2011] [23:51:44] <jonez> RhodiumToad, what is uncle bob's nick? [Tuesday 21 June 2011] [23:51:56] <RhodiumToad> cga: you're not making any sense [Tuesday 21 June 2011] [23:52:11] <cga> oh gosh i got it all worng again [Tuesday 21 June 2011] [23:52:26] <RhodiumToad> http://en.wikipedia.org/wiki/Bob%27s_your_uncle [Tuesday 21 June 2011] [23:53:08] <merlinm> karlpinc: select '6.0221418e23'::float; [Tuesday 21 June 2011] [23:53:26] <merlinm> karlpinc: whoops i read that backwards [Tuesday 21 June 2011] [23:54:18] <cga> RhodiumToad: but looking at this http://pgsql.privatepaste.com/638ae3a179 it seemed you agreed and this http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-SERIAL seems to tell what i understood , please let me understand. i'm confused. [Tuesday 21 June 2011] [23:54:26] <merlinm> hm, select to_char(0.0004859, '9.99EEEE'); works for me on 9,0 [Tuesday 21 June 2011] [23:54:59] <RhodiumToad> cga: the term "optimize" isn't really appropriate. [Tuesday 21 June 2011] [23:55:11] <karlpinc> merlinm: Nope. That's not what I'm after. I _always_ want the output to be in scientific notation. Coerecing to float only makes this happen sometimes. [Tuesday 21 June 2011] [23:55:24] <cga> ok sorry. my english is not perfect (i'm italian) [Tuesday 21 June 2011] [23:55:25] <RhodiumToad> cga: if you want a table with a generated numeric key, there are two basic equivalent ways: [Tuesday 21 June 2011] [23:56:05] <RhodiumToad> cga: create sequence "foo_id_seq"; create table foo (id integer default nextval('foo_id_seq')); [optionally] alter sequence "foo_id_seq" owned by foo.id; [Tuesday 21 June 2011] [23:56:12] <RhodiumToad> cga: or, [Tuesday 21 June 2011] [23:56:19] <RhodiumToad> cga: create table foo (id serial); [Tuesday 21 June 2011] [23:56:33] <RhodiumToad> cga: since the second way is less typing, it's usually preferred [Tuesday 21 June 2011] [23:57:05] <jonez> RhodiumToad, *doh* well played [Tuesday 21 June 2011] [23:57:06] <cga> RhodiumToad: ok agreed. [Tuesday 21 June 2011] [23:57:08] <RhodiumToad> cga: other than that the two methods are equivalent, assuming you included the alter sequence command [Tuesday 21 June 2011] [23:57:48] <DrLou> cga: Buona Sera! [Tuesday 21 June 2011] [23:57:51] <RhodiumToad> cga: (if you don't include that, then the sequence isn't dropped if the table or column is. this may be appropriate if you're using one sequence for several things, but that's definitely an advanced usage) [Tuesday 21 June 2011] [23:58:21] <cga> RhodiumToad: agree on that too, in the original schema theyu don't alter or owned by [Tuesday 21 June 2011] [23:58:53] <cga> RhodiumToad: that was my try (to put it on CREATE TABLE foo OWNED BY; [Tuesday 21 June 2011] [23:59:04] <RhodiumToad> cga: the "owned by" thing is what makes pg_get_serial_sequence work, so you should always do it for sequences that are dedicated to a specific column [Tuesday 21 June 2011] [23:59:17] <cga> ok [Tuesday 21 June 2011] [23:59:32] <RhodiumToad> cga: but again, serial or bigserial do that for you [Tuesday 21 June 2011] [23:59:51] <cga> ok i'm following so far [Tuesday 21 June 2011] [23:59:58] * cga rolls up a fag [Wednesday 22 June 2011] [00:01:38] <cga> RhodiumToad: i'm following so far. but just here my doubts begin. [Wednesday 22 June 2011] [00:01:54] <RhodiumToad> cga: doubts about what? [Wednesday 22 June 2011] [00:01:58] <merlinm> karlpinc: 8.1 is out of support [Wednesday 22 June 2011] [00:02:07] <karlpinc> merlinm: Yes. [Wednesday 22 June 2011] [00:03:15] <cga> RhodiumToad: what i misunderstand is : if method 1 uses the long query with a type bigint and method 2 makes it all "automagic" with the use of bigserial instead of alter or owned. i assume it implies that bigint is alwyas translated to bigserial and integer to serial, hence my questions/confusion/doubts. [Wednesday 22 June 2011] [00:03:54] <RhodiumToad> cga: I'm not understanding the problem. [Wednesday 22 June 2011] [00:04:27] * cga operator..... yes? .... i need to learn english! [Wednesday 22 June 2011] [00:04:34] <cga> :P [Wednesday 22 June 2011] [00:04:54] <merlinm> karlpinc: upgrading from 8.3 to version newer than 8.3 is usually pretty painless. unfortunately coming from earlier version than that you could bump into text cast issues [Wednesday 22 June 2011] [00:05:57] <RhodiumToad> cga: bigint is a data type (64-bit signed integer). integer is a data type (32-bit signed integer). bigserial and serial are shorthand ways to say "bigint/integer default nextval('some_sequence_I_just_made')" and NOTHING ELSE. [Wednesday 22 June 2011] [00:05:58] <cga> RhodiumToad: this whole "discussion" and doubts was born from my previous question. "should i always 'translate' bigint to bgiserial and integer to serial bla bla " that i asked before. [Wednesday 22 June 2011] [00:06:36] <RhodiumToad> cga: no. you should leave them alone except when there's also a default nextval(whatever) [Wednesday 22 June 2011] [00:06:59] <cga> RhodiumToad: we are in that exception for all the cases i'm asking for [Wednesday 22 June 2011] [00:07:18] <merlinm> cga: one reason not to use serial types and do it manually is if you wanted to share a sequence among multiple tables [Wednesday 22 June 2011] [00:07:31] <cga> merlinn not the case, thank you =( [Wednesday 22 June 2011] [00:07:35] <cga> errr =) [Wednesday 22 June 2011] [00:07:41] <RhodiumToad> merlinm: that's strictly advanced users only :-) [Wednesday 22 June 2011] [00:07:51] <cga> which i'm not [Wednesday 22 June 2011] [00:07:59] <merlinm> RhodiumToad: heh, i'm just planting the seed [Wednesday 22 June 2011] [00:08:34] <cga> so RhodiumToad, can we concluded that my question wasn;'t taht wrong (simply badly asked?) acknowledged that we are in that exception? [Wednesday 22 June 2011] [00:09:10] <cga> cani try to ask again? :P [Wednesday 22 June 2011] [00:09:57] <merlinm> cga: i'm still trying to figure out exactly what your question is [Wednesday 22 June 2011] [00:09:57] <RhodiumToad> sure [Wednesday 22 June 2011] [00:10:40] <cga> considering that we are talking about only CRATE SEQUENCE with the use of nextval() , should i change BIGINT to BIGSERIAL and INTEGER to SERIAL , respecting this (sort of) direct relation? [Wednesday 22 June 2011] [00:11:04] * cga hides [Wednesday 22 June 2011] [00:11:46] <merlinm> cga: if you are talking about wanted to use automatic incrementing keys, then yes [Wednesday 22 June 2011] [00:11:54] <merlinm> wanting* [Wednesday 22 June 2011] [00:12:00] <cga> yes! [Wednesday 22 June 2011] [00:12:21] <merlinm> rock out [Wednesday 22 June 2011] [00:12:28] <cga> thank you RhodiumToad merlinn for your great patience and preciuos help =) [Wednesday 22 June 2011] [00:13:13] <cga> now comes the second part of the question :P [Wednesday 22 June 2011] [00:13:27] <merlinm> with baited breath i await thee [Wednesday 22 June 2011] [00:14:13] <cga> (keep in mind the last q/a): woudln;t it be better (speaking of keys and seuqences liek that) to make them all BIGSERIAL instead? [Wednesday 22 June 2011] [00:15:09] <cga> i ask this because: [Wednesday 22 June 2011] [00:15:35] <merlinm> cga: no harm in just doing bigserial these days. it most cases it really doesn't matter [Wednesday 22 June 2011] [00:16:13] <merlinm> if you are working with potentially big data or have strict performance requirements it matters [Wednesday 22 June 2011] [00:16:17] <cga> if i'm to use such sequences is because i need them for big number of anything related to them. since we are taling about blog w/ possibly many use of key... why don;t use the same measure on all of them? [Wednesday 22 June 2011] [00:16:42] <RhodiumToad> no blog will ever need bigserial rather than serial [Wednesday 22 June 2011] [00:16:48] <cga> true indeed [Wednesday 22 June 2011] [00:17:04] <RhodiumToad> stick to serial unless there's actually some possibility it won't be enough. [Wednesday 22 June 2011] [00:17:23] <cga> but the original schema has mixed sequence with mixed "value" (big|int) [Wednesday 22 June 2011] [00:17:27] <cga> ok [Wednesday 22 June 2011] [00:17:30] <merlinm> more and more lately i just use bigserial for everything [Wednesday 22 June 2011] [00:17:48] <RhodiumToad> fine if you're on 64-bit platforms and have unlimited space :-) [Wednesday 22 June 2011] [00:18:12] <cga> eh, 32 is not allowed to install this blog engine :P (joking) [Wednesday 22 June 2011] [00:18:20] <merlinm> sure. also *when I'm using surrogate keys, which is typically never [Wednesday 22 June 2011] [00:18:46] <cga> let me understand: serial|integer is about millions and big is about trillions ? [Wednesday 22 June 2011] [00:18:50] <merlinm> well, maybe 30-40% in reality [Wednesday 22 June 2011] [00:19:02] <RhodiumToad> cga: no [Wednesday 22 June 2011] [00:19:05] <cga> arf [Wednesday 22 June 2011] [00:19:09] <RhodiumToad> cga: integer goes up to 2 billion [Wednesday 22 June 2011] [00:19:10] <merlinm> billions/quintillions [Wednesday 22 June 2011] [00:19:23] <cga> i think billions are more than enough for a blog [Wednesday 22 June 2011] [00:19:28] <andres> merlinm: especially since int8 is passed by value on 64bytes these days... [Wednesday 22 June 2011] [00:19:29] <cga> cool =_ [Wednesday 22 June 2011] [00:19:51] <merlinm> andres: yup -- naturally [Wednesday 22 June 2011] [00:19:57] <cga> RhodiumToad: on both arch ? [Wednesday 22 June 2011] [00:20:11] <RhodiumToad> cga: yes [Wednesday 22 June 2011] [00:20:42] <cga> ok fair enough, same measure (SERIAL) will be used. a blog with billions of posts... never seen. [Wednesday 22 June 2011] [00:21:12] <merlinm> cga: if you are this analytical about all things, you should have some interesting blog posts :-) [Wednesday 22 June 2011] [00:21:37] <cga> merlinn one day may be :P [Wednesday 22 June 2011] [00:22:07] <cga> and thanks for the pleasant compliment sir [Wednesday 22 June 2011] [00:22:55] * cga 30yrs later: damn.... this is ipv4 vs ipv6 all over again..... i should have used BIGSERIAL!!! :P [Wednesday 22 June 2011] [00:23:05] <RhodiumToad> heh [Wednesday 22 June 2011] [00:23:36] <cga> anda agin: thank you RhodiumToad merlinn for your great patience and preciuos help =) [Wednesday 22 June 2011] [00:23:37] <RhodiumToad> slashdot got bitten by that, but in their case they'd used a 24-bit (not 32-bit) field for comment ids [Wednesday 22 June 2011] [00:23:47] <cga> not it's time for some sleep [Wednesday 22 June 2011] [00:23:54] <RhodiumToad> so after 16 million comments they got stuck [Wednesday 22 June 2011] [00:23:58] <cga> hu [Wednesday 22 June 2011] [00:24:01] <merlinm> "oops [Wednesday 22 June 2011] [00:24:50] <cga> darn [Wednesday 22 June 2011] [00:25:00] <cga> now you got some more doubt instilled into my brain [Wednesday 22 June 2011] [00:25:09] <RhodiumToad> think of it this way: integer is big enough for something to happen every second for 68 years [Wednesday 22 June 2011] [00:25:12] <merlinm> I err on the liberal side of everything in my schema. My co-workers view my with mix of disbelief and suspicious when I argue to use 'text' instead of varchar(n) [Wednesday 22 June 2011] [00:25:22] <cga> RhodiumToad: great =) [Wednesday 22 June 2011] [00:25:38] <merlinm> Flanker: sure. dont use them [Wednesday 22 June 2011] [00:25:39] <RhodiumToad> best advice for rules: don't use them [Wednesday 22 June 2011] [00:25:49] * RhodiumToad is an expert on rules [Wednesday 22 June 2011] [00:26:05] <cga> second rules of pgsql club: hter are no rules [Wednesday 22 June 2011] [00:26:14] * RhodiumToad considers himself unable to reliably write nontrivial rules that always work [Wednesday 22 June 2011] [00:26:15] <cga> s/hter/there [Wednesday 22 June 2011] [00:26:44] <cga> night folks, and thanks for all the fish [Wednesday 22 June 2011] [00:26:51] <cga> see you tomorrow [Wednesday 22 June 2011] [00:26:52] <merlinm> Flanker: I would run production system on 9.1 beta just to avoid requirement of using rules [Wednesday 22 June 2011] [00:26:58] <merlinm> cga later [Wednesday 22 June 2011] [00:27:09] <RhodiumToad> Flanker: what do you need to do with them? [Wednesday 22 June 2011] [19:06:37] Join You have joined the channel #postgresql ([email protected]). [Wednesday 22 June 2011] [19:07:36] <RhodiumToad> or make it an sql function [Wednesday 22 June 2011] [19:09:18] <RhodiumToad> no RETURN keyword in sql functions [Wednesday 22 June 2011] [19:09:34] <RhodiumToad> the body is just $$ select count("ID") from "Edges"; $$ [Wednesday 22 June 2011] [19:55:38] <RhodiumToad> pg_lizard: be aware that access to the log files potentially gives privilege escalation to the postgres user [Wednesday 22 June 2011] [19:56:05] <pg_lizard> yea RhodiumToad. Thanks. [Wednesday 22 June 2011] [19:56:34] <dim> RhodiumToad: privilege escalation? via read-only access? [Wednesday 22 June 2011] [19:56:51] <RhodiumToad> i.e. you don't want to give read access to the logfiles to anyone you wouldn't give permission to sudo -u postgres [Wednesday 22 June 2011] [19:57:07] <RhodiumToad> dim: passwords are exposed in the logs. [Wednesday 22 June 2011] [19:57:35] <RhodiumToad> dim: as soon as any superuser changes his password... boom [Wednesday 22 June 2011] [19:57:36] <dim> RhodiumToad: oh, I tend to use trust on private networks with VPN auth, so that that's not my problem to solve [Wednesday 22 June 2011] [19:57:50] <intgr> RhodiumToad: Depends on your log options obviously [Wednesday 22 June 2011] [19:58:20] <RhodiumToad> if you have log_statement set to anything other than none... [Wednesday 22 June 2011] [20:08:28] <RhodiumToad> hashagg doesn't actually limit itself to work_mem (this is a bug) [Wednesday 22 June 2011] [20:09:02] <RhodiumToad> the planner won't plan a hashagg if the expected memory usage is >work_mem, but if it plans one, then at runtime it will use as much memory as it needs [Wednesday 22 June 2011] [20:10:39] <RhodiumToad> maybe your aggregate functions have expensive finalfns? [Wednesday 22 June 2011] [20:11:31] <RhodiumToad> hm [Wednesday 22 June 2011] [20:11:41] <RhodiumToad> that's ... odd. [Wednesday 22 June 2011] [20:12:12] <RhodiumToad> count(*) is about the cheapest transfn and I don't think it even has a finalfn [Wednesday 22 June 2011] [20:12:47] <threshar> RhodiumToad: I'm wondering if it is some memory bandwidth problem again. (i've hit similar things in the past). but 40k rows isn't much data to slog. [Wednesday 22 June 2011] [20:13:10] <RhodiumToad> unless the values of a and b are very large [Wednesday 22 June 2011] [20:13:49] <threshar> RhodiumToad: a is an int, b is a 4 character string. [Wednesday 22 June 2011] [20:14:08] <RhodiumToad> well, that's nothing [Wednesday 22 June 2011] [20:20:07] <RhodiumToad> nasty [Wednesday 22 June 2011] [20:20:12] <RhodiumToad> how did you get that? [Wednesday 22 June 2011] [20:21:20] <RhodiumToad> what pg version? [Wednesday 22 June 2011] [20:22:26] <RhodiumToad> hm [Wednesday 22 June 2011] [20:24:29] <RhodiumToad> bill_c: you might be able to bypass that error by deleting something that the view depends on, with CASCADE [Wednesday 22 June 2011] [20:25:11] <bill_c> RhodiumToad, only thing it depends on is the schema, though not sure that schema is still used [Wednesday 22 June 2011] [20:25:18] <RhodiumToad> bill_c: no tables? [Wednesday 22 June 2011] [20:25:29] <RhodiumToad> bill_c: or functions? [Wednesday 22 June 2011] [20:26:13] <RhodiumToad> bill_c: you could move everything else (if anything) out of the schema and then drop the schema [Wednesday 22 June 2011] [20:36:30] <RhodiumToad> a cascaded delete is almost certainly the easiest and safest way [Wednesday 22 June 2011] [20:36:43] <RhodiumToad> updating pg_rewrite directly is a bit more hairy [Wednesday 22 June 2011] [20:37:58] Quit merlinm has left this server (Ping timeout: 252 seconds). [Wednesday 22 June 2011] [20:38:09] <RhodiumToad> what distro? [Wednesday 22 June 2011] [20:38:25] <bill_c> RhodiumToad, rPath [Wednesday 22 June 2011] [20:38:32] <RhodiumToad> iirc there were some old ones (gentoo?) that had the recursive query patch too [Wednesday 22 June 2011] [20:38:57] <RhodiumToad> bill_c: can you pastebin the output of select * from pg_rewrite where ev_class='viewname'::regclass;
-- CREATE SEQUENCE fixes:
-- correct syntax and use of CREATE SEQUENCE w/ the use of SERIAL
-- Some minor improvements all over CREATE TABLE where:
-- use of INTEGER instead of BIGINT where appropriate. BIGINT it's just a waste.
-- TODO w/ next patch.
-- optimize code and fix the keyvil stuff w/ things that
-- are really needed for a PKEY and that Habari checks as Unique/PKEY.
CREATE TABLE {$prefix}posts (
id SERIAL,
slug VARCHAR(255) NOT NULL,
content_type INTEGER NOT NULL,
title VARCHAR(255) NOT NULL,
guid VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
cached_content TEXT NOT NULL,
user_id INTEGER NOT NULL,
status INTEGER NOT NULL,
pubdate INTEGER NOT NULL,
updated INTEGER NOT NULL,
modified INTEGER NOT NULL,
PRIMARY KEY (id),
UNIQUE (slug)
);
CREATE TABLE {$prefix}postinfo (
post_id INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
type INTEGER NOT NULL DEFAULT 0,
value TEXT,
PRIMARY KEY (post_id,name)
);
CREATE TABLE {$prefix}posttype (
id SERIAL,
name VARCHAR(255) NOT NULL,
active SMALLINT DEFAULT 1,
PRIMARY KEY (id)
);
CREATE TABLE {$prefix}poststatus (
id SERIAL,
name VARCHAR(255) NOT NULL,
internal SMALLINT,
PRIMARY KEY (id)
);
CREATE TABLE {$prefix}options (
name VARCHAR(255) NOT NULL,
type INTEGER NOT NULL DEFAULT 0,
value TEXT,
PRIMARY KEY (name)
);
CREATE TABLE {$prefix}users (
id SERIAL,
username VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE (username)
);
CREATE TABLE {$prefix}userinfo (
user_id SMALLINT NOT NULL,
name VARCHAR(255) NOT NULL,
type INTEGER NOT NULL DEFAULT 0,
value TEXT,
PRIMARY KEY (user_id,name)
);
CREATE TABLE {$prefix}tags (
id SERIAL,
tag_text VARCHAR(255) NOT NULL,
tag_slug VARCHAR(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE (tag_slug)
);
CREATE TABLE {$prefix}tag2post (
tag_id INTEGER NOT NULL,
post_id INTEGER NOT NULL,
PRIMARY KEY (tag_id,post_id)
);
CREATE INDEX {$prefix}tag2post_post_id_key ON {$prefix}tag2post (
post_id
);
CREATE TABLE {$prefix}comments (
id SERIAL,
post_id INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
url VARCHAR(255) NULL,
ip INTEGER NOT NULL,
content TEXT,
status INTEGER NOT NULL,
date INT NOT NULL,
type INTEGER NOT NULL,
PRIMARY KEY (id)
);
CREATE INDEX {$prefix}comments_post_id_key ON {$prefix}comments (
post_id
);
CREATE TABLE {$prefix}commentinfo (
comment_id INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
type INTEGER NOT NULL DEFAULT 0,
value TEXT NULL,
PRIMARY KEY (comment_id,name)
);
CREATE TABLE {$prefix}rewrite_rules (
rule_id SERIAL,
name VARCHAR(255) NOT NULL,
parse_regex VARCHAR(255) NOT NULL,
build_str VARCHAR(255) NOT NULL,
handler VARCHAR(255) NOT NULL,
action VARCHAR(255) NOT NULL,
priority INTEGER NOT NULL,
is_active INTEGER NOT NULL DEFAULT 0,
rule_class INTEGER NOT NULL DEFAULT 0,
description TEXT NULL,
parameters TEXT NULL,
PRIMARY KEY (rule_id)
);
CREATE TABLE {$prefix}crontab (
cron_id SERIAL,
name VARCHAR(255) NOT NULL,
callback VARCHAR(255) NOT NULL,
last_run INTEGER,
next_run INTEGER NOT NULL,
increment INTEGER NOT NULL,
start_time INTEGER NOT NULL,
end_time INTEGER,
result VARCHAR(255) NOT NULL,
notify VARCHAR(255) NOT NULL,
cron_class SMALLINT NOT NULL DEFAULT 0,
description TEXT NULL,
PRIMARY KEY (cron_id)
);
CREATE TABLE {$prefix}log (
id SERIAL,
user_id INTEGER NULL DEFAULT NULL,
type_id INTEGER NOT NULL,
severity_id SMALLINT NOT NULL,
message VARCHAR(255) NOT NULL,
data BYTEA NULL,
timestamp INT NOT NULL,
ip INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (id)
);
CREATE TABLE {$prefix}log_types (
id SERIAL,
module VARCHAR(100) NOT NULL,
type VARCHAR(100) NOT NULL,
PRIMARY KEY (id),
UNIQUE (module,type)
);
CREATE TABLE {$prefix}groups (
id SERIAL,
name VARCHAR(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE (name)
);
CREATE TABLE {$prefix}users_groups (
id SERIAL,
user_id INTEGER NOT NULL,
group_id INTEGER NOT NULL,
PRIMARY KEY (id),
UNIQUE (user_id,group_id)
);
CREATE TABLE {$prefix}sessions (
token varchar(255) NOT NULL,
subnet INTEGER NOT NULL DEFAULT 0,
expires INTEGER NOT NULL DEFAULT 0,
ua VARCHAR(255) NOT NULL,
data TEXT,
user_id INTEGER,
PRIMARY KEY (token)
);
CREATE TABLE {$prefix}terms (
id SERIAL,
term VARCHAR(255) NOT NULL,
term_display VARCHAR(255) NOT NULL,
vocabulary_id INTEGER NOT NULL,
mptt_left INTEGER NOT NULL,
mptt_right INTEGER NOT NULL,
PRIMARY KEY (id),
UNIQUE (vocabulary_id,mptt_right,mptt_left),
UNIQUE (vocabulary_id,term)
);
CREATE TABLE {$prefix}terminfo (
term_id INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
type INTEGER NOT NULL DEFAULT 0,
value TEXT NULL,
PRIMARY KEY (term_id,name)
);
CREATE TABLE {$prefix}vocabularies (
id SERIAL,
name VARCHAR(255) NOT NULL,
description TEXT,
features TEXT,
PRIMARY KEY (id)
);
CREATE TABLE {$prefix}object_terms (
object_id INTEGER NOT NULL,
term_id INTEGER NOT NULL,
object_type_id INTEGER NOT NULL,
PRIMARY KEY (object_id, term_id)
);
CREATE TABLE {$prefix}object_types (
id SERIAL,
name VARCHAR(50),
PRIMARY KEY (id)
);
INSERT INTO {$prefix}object_types (name) VALUES ('post');
CREATE TABLE {$prefix}tokens (
id SERIAL,
name VARCHAR(255) NOT NULL,
description VARCHAR(255) NULL,
token_type INT NOT NULL DEFAULT 0,
token_group VARCHAR(255) NULL,
PRIMARY KEY (id),
UNIQUE (name)
);
CREATE TABLE {$prefix}post_tokens (
post_id INTEGER NOT NULL,
token_id INTEGER NOT NULL,
PRIMARY KEY (post_id, token_id)
);
CREATE TABLE {$prefix}group_token_permissions (
group_id INTEGER NOT NULL,
token_id INTEGER NOT NULL,
access_mask SMALLINT NOT NULL,
PRIMARY KEY (group_id, token_id)
);
CREATE TABLE {$prefix}user_token_permissions (
user_id INTEGER NOT NULL,
token_id INTEGER NOT NULL,
access_mask SMALLINT NOT NULL,
PRIMARY KEY (user_id, token_id)
);
CREATE TABLE {$prefix}scopes (
id SERIAL,
name VARCHAR(255) NOT NULL,
criteria TEXT NOT NULL,
description TEXT NULL,
priority SMALLINT NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE {$prefix}blocks (
id SERIAL,
title VARCHAR(255) NOT NULL,
type VARCHAR(255) NOT NULL,
data TEXT NULL,
PRIMARY KEY (id)
);
CREATE TABLE {$prefix}blocks_areas (
id SERIAL,
block_id INTEGER NOT NULL,
area VARCHAR(255) NOT NULL,
scope_id INTEGER NOT NULL,
PRIMARY KEY (id),
display_order INTEGER NOT NULL DEFAULT 0
);
1,9c1
< -- CREATE SEQUENCE fixes:
< -- correct syntax and use of CREATE SEQUENCE w/ the use of SERIAL
< -- Some minor improvements all over CREATE TABLE where:
< -- use of INTEGER instead of BIGINT where appropriate. BIGINT it's just a waste.
<
< -- TODO w/ next patch.
< -- optimize code and fix the keyvil stuff w/ things that
< -- are really needed for a PKEY and that Habari checks as Unique/PKEY.
<
---
> CREATE SEQUENCE {$prefix}posts_pkey_seq;
11c3
< id SERIAL,
---
> id BIGINT NOT NULL DEFAULT nextval('{$prefix}posts_pkey_seq'),
28c20
< post_id INTEGER NOT NULL,
---
> post_id BIGINT NOT NULL,
34a27
> CREATE SEQUENCE {$prefix}posttype_pkey_seq;
36c29
< id SERIAL,
---
> id INTEGER NOT NULL DEFAULT nextval('{$prefix}posttype_pkey_seq'),
41a35
> CREATE SEQUENCE {$prefix}poststatus_pkey_seq;
43c37
< id SERIAL,
---
> id INTEGER NOT NULL DEFAULT nextval('{$prefix}poststatus_pkey_seq'),
55a50
> CREATE SEQUENCE {$prefix}users_pkey_seq;
57c52
< id SERIAL,
---
> id INTEGER NOT NULL DEFAULT nextval('{$prefix}users_pkey_seq'),
72a68
> CREATE SEQUENCE {$prefix}tags_pkey_seq;
74c70
< id SERIAL,
---
> id BIGINT NOT NULL DEFAULT nextval('{$prefix}tags_pkey_seq'),
82,83c78,79
< tag_id INTEGER NOT NULL,
< post_id INTEGER NOT NULL,
---
> tag_id BIGINT NOT NULL,
> post_id BIGINT NOT NULL,
90a87
> CREATE SEQUENCE {$prefix}comments_pkey_seq;
92,93c89,90
< id SERIAL,
< post_id INTEGER NOT NULL,
---
> id BIGINT NOT NULL DEFAULT nextval('{$prefix}comments_pkey_seq'),
> post_id BIGINT NOT NULL,
97c94
< ip INTEGER NOT NULL,
---
> ip BIGINT NOT NULL,
110c107
< comment_id INTEGER NOT NULL,
---
> comment_id BIGINT NOT NULL,
116a114
> CREATE SEQUENCE {$prefix}rewrite_rules_pkey_seq;
118c116
< rule_id SERIAL,
---
> rule_id INTEGER NOT NULL DEFAULT nextval('{$prefix}rewrite_rules_pkey_seq'),
131a130
> CREATE SEQUENCE {$prefix}crontab_pkey_seq;
133c132
< cron_id SERIAL,
---
> cron_id INTEGER NOT NULL DEFAULT nextval('{$prefix}crontab_pkey_seq'),
147a147
> CREATE SEQUENCE {$prefix}log_pkey_seq;
149c149
< id SERIAL,
---
> id BIGINT NOT NULL DEFAULT nextval('{$prefix}log_pkey_seq'),
156c156
< ip INTEGER NOT NULL DEFAULT 0,
---
> ip BIGINT NOT NULL DEFAULT 0,
159a160
> CREATE SEQUENCE {$prefix}log_types_pkey_seq;
161c162
< id SERIAL,
---
> id INTEGER NOT NULL DEFAULT nextval('{$prefix}log_types_pkey_seq'),
167a169
> CREATE SEQUENCE {$prefix}groups_pkey_seq;
169c171
< id SERIAL,
---
> id INTEGER NOT NULL DEFAULT nextval('{$prefix}groups_pkey_seq'),
174a177
> CREATE SEQUENCE {$prefix}users_groups_pkey_seq;
176c179
< id SERIAL,
---
> id INTEGER NOT NULL DEFAULT nextval('{$prefix}users_groups_pkey_seq'),
186c189
< expires INTEGER NOT NULL DEFAULT 0,
---
> expires BIGINT NOT NULL DEFAULT 0,
192a196
> CREATE SEQUENCE {$prefix}terms_pkey_seq;
194c198
< id SERIAL,
---
> id INTEGER NOT NULL DEFAULT nextval('{$prefix}terms_pkey_seq'),
206c210
< term_id INTEGER NOT NULL,
---
> term_id BIGINT NOT NULL,
212a217
> CREATE SEQUENCE {$prefix}vocabularies_pkey_seq;
214c219
< id SERIAL,
---
> id INTEGER NOT NULL DEFAULT nextval('{$prefix}vocabularies_pkey_seq'),
217c222
< features TEXT,
---
> features TEXT,
227a233
> CREATE SEQUENCE {$prefix}object_types_pkey_seq;
229c235
< id SERIAL,
---
> id INTEGER NOT NULL DEFAULT nextval('{$prefix}object_types_pkey_seq'),
235a242
> CREATE SEQUENCE {$prefix}tokens_pkey_seq;
237c244
< id SERIAL,
---
> id INTEGER NOT NULL DEFAULT nextval('{$prefix}tokens_pkey_seq'),
265a273
> CREATE SEQUENCE {$prefix}scopes_pkey_seq;
267c275
< id SERIAL,
---
> id INTEGER NOT NULL DEFAULT nextval('{$prefix}scopes_pkey_seq'),
274a283
> CREATE SEQUENCE {$prefix}blocks_pkey_seq;
276c285
< id SERIAL,
---
> id INTEGER NOT NULL DEFAULT nextval('{$prefix}blocks_pkey_seq'),
282a292
> CREATE SEQUENCE {$prefix}blocks_areas_pkey_seq;
284c294
< id SERIAL,
---
> id INTEGER NOT NULL DEFAULT nextval('{$prefix}blocks_areas_pkey_seq'),
signature.asc
Description: This is a digitally signed message part.
