[GENERAL] Resetting serial type after delete from table
If I delete from table, which table contains a serial type field, and then insert new rows into the table excluding the [serial] column from the list of columns in the INSERT statement, the numbers in the serial column resume where they left off prior to the delete from table: 639, 640, 641, 642 for example. This behavior is totally acceptable, but is it possible to have the serial column reset itself to 1 following delete from table (i.e. following flushing all the rows from the table)? The only way I can think to do this is by altering the table by dropping the serial column and then altering it again by adding a new serial column before doing the insert. That is only a couple of more lines of script, so I don't do the work, but is there an easier way? Thanks, John
Re: [GENERAL] Resetting serial type after delete from table
On Saturday 8. May 2010 10.11.32 John Gage wrote: If I delete from table, which table contains a serial type field, and then insert new rows into the table excluding the [serial] column from the list of columns in the INSERT statement, the numbers in the serial column resume where they left off prior to the delete from table: 639, 640, 641, 642 for example. This behavior is totally acceptable, but is it possible to have the serial column reset itself to 1 following delete from table (i.e. following flushing all the rows from the table)? The only way I can think to do this is by altering the table by dropping the serial column and then altering it again by adding a new serial column before doing the insert. That is only a couple of more lines of script, so I don't do the work, but is there an easier way? http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE- SERIAL http://www.postgresql.org/docs/current/static/functions-sequence.html regards, -- Leif Biberg Kristensen http://solumslekt.org/blog/ -- 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] Resetting serial type after delete from table
Thanks very, very much. I got as far as 8.1.4 and did not find 9.15. May I suggest that the documentation have an index entry under serial for 9.15, which is a major heading whereas 8.1.4 is a minor heading and has its own index entry? This is said from the perspective of awe for the documentation. John On May 8, 2010, at 10:30 AM, Leif Biberg Kristensen wrote: http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE- SERIAL http://www.postgresql.org/docs/current/static/functions-sequence.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Documentation availability as a single page of text
Is the documentation available anywhere as a single page text file? This would be enormously helpful for searching using regular expressions in Vim, for example, or excerpting pieces for future reference. John -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Lightning Talks for PgCon! Submit yours today.
Hi! We're having Lightning Talks again at PgCon - scheduled for 5:30pm on May 20th in Ottawa! Do you have a talk or idea you'd like to share? Lightning Talks are one of the most highly attended sessions because they are fast, fun, and useful (but not always). Slides are not required. If you use them, you'll have to operate them as PDFs. Please send your 5-minute talk idea to li...@pgcon.org. Slots fill up fast, so get them in now! We'll accept submissions until May 16 via email, and after that, you'll need to find me (Selena) at the conference if you'd like to be added. We can only accept 11 talks in the time allowed. Selection is generally first-come, first-served. I will not determine the order of the talks until the time of the session. More details are at: http://www.pgcon.org/2010/schedule/events/267.en.html -- http://chesnok.com/daily - me http://endpoint.com - work -- 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] psql weird behaviour with charset encodings
hernan gonzalez hgonza...@gmail.com writes:
Sorry about a error in my previous example (mixed width and precision).
But the conclusion is the same - it works on bytes:
This example works like that because it's running in C locale always.
Try something like this:
#includestdio.h
#includelocale.h
int main () {
char s[] = ni\xc3qo; /* 5 bytes , not valid utf8 */
setlocale(LC_ALL, );
printf(|%.*s|\n,3,s);
return 0;
}
I get different (and undesirable) effects depending on LANG.
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] Invitation to connect on LinkedIn
LinkedIn Paulo moraes requested to add you as a connection on LinkedIn: -- Andrew, I'd like to add you to my professional network on LinkedIn. - Paulo Accept invitation from Paulo moraes http://www.linkedin.com/e/ATaEtqAFKHyclb4yaP2gMVdFpLR9OBj5WwZ3C3FunFK/blk/I2024701665_2/1BpC5vrmRLoRZcjkkZt5YCpnlOt3RApnhMpmdzgmhxrSNBszYOnPkSdz4MdPgOc399bS5it49Ni4dpbP0Sc3sTdzgUcz4LrCBxbOYWrSlI/EML_comm_afe/ View invitation from Paulo moraes http://www.linkedin.com/e/ATaEtqAFKHyclb4yaP2gMVdFpLR9OBj5WwZ3C3FunFK/blk/I2024701665_2/39vdjoScj0Td38McAALqnpPbOYWrSlI/svi/ -- DID YOU KNOW you can be the first to know when a trusted member of your network changes jobs? With Network Updates on your LinkedIn home page, you'll be notified as members of your network change their current position. Be the first to know and reach out! http://www.linkedin.com/ -- (c) 2010, LinkedIn Corporation
[GENERAL] Query Issue with full-text search
This may better-belong in pgsql-sql but since it deals with a function
as opposed to raw SQL syntax I am sticking it here
Consider the following DBMS schema slice
Table public.post
Column | Type |
Modifiers
---+--+
subject | text |
message | text |
ordinal | integer | not null default
nextval('post_ordinal_seq'::regclass)
Indexes:
post_pkey PRIMARY KEY, btree (ordinal)
idx_message gin (to_tsvector('english'::text, message))
idx_subject gin (to_tsvector('english'::text, subject))
(there are a bunch more indices and columns in the table, but these are
the ones in question)
Now let's run a couple of queries on this:
ticker=# explain analyze select * from post where to_tsvector('english',
message) @@ to_tsquery('violence') order by modified desc limit
100;
QUERY
PLAN
Limit (cost=0.00..2456.32 rows=100 width=433) (actual
time=266.703..3046.310 rows=100 loops=1)
- Index Scan Backward using post_modified on post
(cost=0.00..240400.00 rows=9787 width=433) (actual
time=266.698..3045.920 rows=100 loops=1)
Filter: (to_tsvector('english'::text, message) @@
to_tsquery('violence'::text))
Total runtime: 3046.565 ms
(4 rows)
Ok, not too bad, considering that the table contains close to 2 million
rows - ~3 seconds is pretty good.
Now let's try something that's NOT in the database:
ticker=# explain analyze select * from post where to_tsvector('english',
message) @@ to_tsquery('hoseface') order by modified desc limit
100;
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
QUERY
PLAN
---
Limit (cost=0.00..2456.32 rows=100 width=433) (actual
time=303350.036..303350.036 rows=0 loops=1)
- Index Scan Backward using post_modified on post
(cost=0.00..240400.00 rows=9787 width=433) (actual
time=303350.031..303350.031 rows=0 loops=1)
Filter: (to_tsvector('english'::text, message) @@
to_tsquery('hoseface'::text))
Total runtime: 303350.079 ms
(4 rows)
This is **UNBELIEVABLY** slow; indeed, it appears to have done a
sequential scan of the entire table!
Why?
One would think that if the index lookup fails it fails - and would fail
FAST, returning no rows. It appears that this is not the case, and the
system actually goes in and tries to look up the query off the message
contents, IGNORING the index!
That's not good for what should be obvious reasons. is the gin
index type screwed up in some form or fashion?
This behavior is relatively new. I'm running 8.4.3 and this started
happening some time before that - I believe it was an issue in 8.4.2,
but I KNOW it was not a problem when I was running 8.3. The confounding
factor is that the table has grown rapidly and as such not happening
before might be more due to the table size than the software release -
of that I cannot be certain.
The other possibility is that the NOTICE results in some sort of flag
being set that tells the query processor to ignore the index and perform
a sequential scan IF there's a failure to match. If this is the case I
will then have to write something to go through and find the offending
item and remove it.
-- Karl
attachment: karl.vcf
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Database design confusing pg_restore, and misc pg_restore issues
Hey everyone,
I run a website that sells videogames, and different games have
different registration systems, so I have a database design that goes
something like this:
registration_type enum('none', 'regtype1', 'regtype2')
products(product_id, registration_type)
order_item(order_id, product_id, check(order_item_has_reginfo(order_id,
product_id)))
regtype1_reginfo(order_id, product_id, misc rows)
regtype2_reginfo(order_id, product_id, orthogonally misc rows)
function order_item_has_reginfo(text, text) returns boolean as $$
select exists(
select 1 from products where product_id = $2
and (
(reg_type = 'none')
or (reg_type = 'regtype1' and (select exists(select 1
from regtype1_reginfo where order_id = $1 and product_id = $2)))
or (reg_type = 'regtype2' and (select exists(select 1
from regtype2_reginfo where order_id = $1 and product_id = $2)))
)
)
$$ LANGUAGE 'SQL';
In other words, (order_id, product_id) of order_item is a foreign key to
either reginfo1, reginfo2, or nothing, depending on which product it is.
The works really well, until I try to use pg_dump/pg_restore, because it
attempts to restore order_items before the reginfo tables. To get it to
work properly, I need to load the schema, disable the check, load the
data, then re-enable the check.
I'm interested in either a more painless way of importing backups, or a
better design.
Incidentally, using --disable-triggers didn't disable checks, and
--use-list didn't seem to actually work on my dev machine (Vista x64);
it just pretends like everything went fine, without inserting any data.
Here's what PowerShell prints out:
PS D:\projects\backup 'C:\Program Files
(x86)\PostgreSQL\8.4\bin\pg_restore.exe' -v --use-list=backup.list -U blahblah -p
-C backup.db
--
-- PostgreSQL database dump
--
-- Started on 2010-05-07 22:22:02
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
-- Completed on 2010-05-08 01:15:01
--
-- PostgreSQL database dump complete
--
pg_restore.exe : pg_restore: implied data-only restore
At line:1 char:2
+'C:\Program Files (x86)\PostgreSQL\8.4\bin\pg_restore.exe' -v
--use-list=backup.list -U blahblah -p -C backup.db
+ CategoryInfo : NotSpecified: (pg_restore: implied data-only
restore:String) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError
Thanks for your help,
-Rick-
--
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] psql weird behaviour with charset encodings
Wow, you are right, this is bizarre... And it's not that glibc intends to compute the length in unicode chars, it actually counts bytes (c plain chars) -as it should- for computing field widths... But, for some strange reason, when there is some width calculation involved it tries so parse the char[] using the locale encoding (when there's no point in doing it!) and if it fails, it truncates (silently) the printf output. So it seems more a glib bug to me than an interpretion issue (bytes vs chars). I posted some details in stackoverflow: http://stackoverflow.com/questions/2792567/printf-field-width-bytes-or-chars BTW, I understand that postgresql uses locale semantics in the server code. But is this really necessary/appropiate in the client (psql) side? Couldnt we stick with C locale here? -- Hernán J. González http://hjg.com.ar/ -- 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] Documentation availability as a single page of text
John Gage wrote: Is the documentation available anywhere as a single page text file? This would be enormously helpful for searching using regular expressions in Vim, for example, or excerpting pieces for future reference. Uh, no, and no one has ever asked for that. There must be some tool that will dump an HTML tree as a single text file. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.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] Documentation availability as a single page of text
Bruce Momjian wrote: John Gage wrote: Is the documentation available anywhere as a single page text file? This would be enormously helpful for searching using regular expressions in Vim, for example, or excerpting pieces for future reference. Uh, no, and no one has ever asked for that. There must be some tool that will dump an HTML tree as a single text file. Or maybe convert the PDF file to text. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query that produces index information for a Table
Hello All, I am trying to pull together some general information about indices (indexes?) for a particular table. I need the following: Index Name, Table Name, Column Name, Unique/Non-Unique, and ordinal position in the index. The information_schema.key_column_usage gets me most of the way there, I think, but does not tell me whether the index is unique and does not seem to differentiate between indices and other types of constraints. I can't believe I am the first to ask this question so I am hoping someone can send me what they use to get this information. Thanks, Craigbert -- I am using the free version of SPAMfighter. We are a community of 7 million users fighting spam. SPAMfighter has removed 1387 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message -- 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] Query that produces index information for a Table
Boyd, Craig cr...@mysoftforge.com writes: I am trying to pull together some general information about indices (indexes?) for a particular table. I need the following: Index Name, Table Name, Column Name, Unique/Non-Unique, and ordinal position in the index. The information_schema.key_column_usage gets me most of the way there, I think, but does not tell me whether the index is unique and does not seem to differentiate between indices and other types of constraints. The information_schema gets you *none* of the way there, actually, because it's a creature of the SQL standard and indexes are outside the standard (yes, really). You can find out about unique constraints from the information_schema views, but not about non-unique indexes, nor even indexes that are unique but weren't created via unique-constraint syntax. If you want to know about all indexes, you'll need to get your hands dirty with looking at the PG system catalogs. I'd suggest looking at the queries psql generates for \dt (use psql -E to watch these) and then modifying them to suit your purposes. 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] psql weird behaviour with charset encodings
hernan gonzalez hgonza...@gmail.com writes: BTW, I understand that postgresql uses locale semantics in the server code. But is this really necessary/appropiate in the client (psql) side? Couldnt we stick with C locale here? As far as that goes, I think we have to turn on that machinery in order to have gettext() work (ie, to have localized error messages). 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] Database design confusing pg_restore, and misc pg_restore issues
Rick Yorgason r...@longbowgames.com writes: In other words, (order_id, product_id) of order_item is a foreign key to either reginfo1, reginfo2, or nothing, depending on which product it is. I think you'll find that few people regard that as good database design. The works really well, until I try to use pg_dump/pg_restore, because it attempts to restore order_items before the reginfo tables. To get it to work properly, I need to load the schema, disable the check, load the data, then re-enable the check. Well, you can hardly expect pg_dump to intuit that there's a dependency there; it understands nothing about the behavior of that SQL function. Since you say that --disable-triggers doesn't help, I guess that you're applying that function not in a trigger but in a CHECK constraint? That's pretty horrid in itself: CHECK is *not* meant to enforce anything except local properties of the newly inserted/updated row itself. Aside from the ordering problems that you've already run into some of, consider what happens when the referenced row gets deleted. (Hint: nothing.) Sure you can't find a way to unify reginfo1/reginfo2 into one table? If you have some side information that doesn't fit conveniently into that table, maybe making an auxiliary table that's foreign-keyed to the master reginfo table would help. But you really need a structure that allows you to declare the order_item table with a regular foreign key for reginfo. Foreign keys are not something you can cobble together from spare parts --- a correct, robust implementation requires magic that is just not available at the user level in SQL. 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] Database design confusing pg_restore, and misc pg_restore issues
On 08/05/2010 10:33 PM, Tom Lane wrote: Since you say that --disable-triggers doesn't help, I guess that you're applying that function not in a trigger but in a CHECK constraint? That's pretty horrid in itself: CHECK is *not* meant to enforce anything except local properties of the newly inserted/updated row itself. Aside from the ordering problems that you've already run into some of, consider what happens when the referenced row gets deleted. (Hint: nothing.) Luckily, they never get deleted :) Okay, well, I guess one solution is to replace the checks with triggers on all tables involved. That's not pretty, and really doesn't express the concept of a constraint very clearly, but I guess it would work. Sure you can't find a way to unify reginfo1/reginfo2 into one table? If you have some side information that doesn't fit conveniently into that table, maybe making an auxiliary table that's foreign-keyed to the master reginfo table would help. But you really need a structure that allows you to declare the order_item table with a regular foreign key for reginfo. So, your first suggestion would look like this: reginfo(order_id, product_id, reginfo1_columns, reginfo2_columns, FOREIGN KEY(order_id, product_id) REFERENCES order_items) For the sake of illustration, let's say that order_item's foreign key to this table is NOT NULL. So, if the product in question uses regtype1, then the reginfo2 columns are NULL, and vice versa. If the product doesn't use any registration, then both the reginfo1 and reginfo2 columns are NULL. The problem is, how do I express that requirement in a constraint? And without updating the schema every time I add a new product? Your second suggestion would look like this: reginfo(order_id, product_id, FOREIGN KEY(order_id, product_id) REFERENCES order_items) reginfo1(order_id, product_id, reginfo1_columns, FOREIGN KEY(order_id, product_id) REFERENCES reginfo) reginfo2(order_id, product_id, reginfo2_columns, FOREIGN KEY(order_id, product_id) REFERENCES reginfo) Well, at that point, the reginfo table is redundant, and the reginfo1 and reginfo2 tables may as well reference order_items directly, which is exactly what I have, minus my problematic constraint. My assumption is that most people would simply give up and assume that this constraint is too difficult to express in SQL, and just rely on the business logic never being wrong. I was hoping that wasn't the case :) Thanks, -Rick- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
