Re: [SQL] Alternative to serial primary key
O D'Arcy J.M. Cain έγραψε στις Jul 7, 2006 : > On Fri, 7 Jul 2006 19:37:15 +1200 > "David Clarke" <[EMAIL PROTECTED]> wrote: > > > And even given all of that, I would probably still use serial. > > Because? > > Simplicity. Cleanliness. > > > > Danger, Will Robinson. The phrase "regenerate my primary key" > > > immediately raises the hairs on the back of my neck. If the primary > > > key can ever change, you have a broken schema. > > > > Perhaps my choice of words was somewhat hasty. A serial is totally > > divorced from the data it represents whereas a md5 hash is (for my > > purposes) unique, stable, verifiable, and simple. > > It's not that I think that the primary key should never have meaning in > the database (I use the two letter country code as the PK in my country > table for example) I just think that it's dangerous ground and should > be tread very carefully. I think some similar "artificial vs natural keys" discussion was active some months ago in the -sql list. I say, 99% of the cases use serials for Primary Keys. They do that they are designed for, and they are robust, and ultra reliable. If someone whishes, then he/she can enforce some Unique Keys as well, on the natural columns. So as a rule of thumb, i do system work (PKs,FKs) with artificial columns, and human work (UKs) (read: prone to change!!!) with natural columns. In my experience, using natural columns for (PKs,FKs) has always resulted in a lost weekend trying to convert to artificial keys. > > However, I join others in applauding you for your efforts to investigate > this so deeply. You may wind up coming out of this with something > interesting, even if it isn't what you went in looking for. > > -- -Achilleus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: RES: [SQL] Joins between int and int[]
O Rodrigo Sakai έγραψε στις Jun 28, 2006 : > Thanks Mantzios, your answer helped a lot! But I have a lot of multi column > foreign keys! Any other ideia?? > You must use/write a function that takes as an argument an array and returns the elements of this array as a set. I dont recall if some function like that exists in contrib but you could search in the archives. Also if my memory serves well there was a similar talk in th -sql list some months ago. Also keep in mind that if your queries are standardized then you can write a program in C/perl/java/php/... that does what you want. If your queries are ad-hoc then go the first approach. > Thanks in advance! > > > -Mensagem original- > De: Achilleus Mantzios [mailto:[EMAIL PROTECTED] > Enviada em: quarta-feira, 28 de junho de 2006 04:01 > Para: Rodrigo Sakai > Assunto: Re: [SQL] Joins between int and int[] > > O Rodrigo Sakai έγραψε στις Jun 27, 2006 : > > > Hi, > > > > > > > > I want to use the system catalog tables/views to query some things, and > > one of the queries I have to do is joining pg_attribute and pg_constraint > to > > know what constraint belongs to which table.attribute. My question is how > > can I write the join clause between a int (pg_attribute.attnum) and int[ ] > > (pg_constraint.conkey). > > Are you having tables with multi column foreign keys? > e.g. (a,b) REFERENCES partable(para,parb). > > If not then join with pg_constraint.conkey[1], > if yes then the problem becomes a little less trivial. > > > > > > > > > > > > > The query is: > > > > > > > > select relname, attname, attnotnull, atthasdef > > > > from pg_class as pc > > > > inner join pg_attribute as pa > > > > on pc.oid=pa.attrelid > > > > inner join pg_constraint pcons > > > > on pc.oid=pcons.conrelid > > > > and pa.attnum = pcons.conkey ---> the problem > > > > > > > > > > > > Thanks!!! > > > > > > > > > > -- -Achilleus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Efficient Searching of Large Text Fields
O Oleg Bartunov έγραψε στις Jun 14, 2006 : > Don't invent a wheel and use contrib/tsearch2 for that. Hi Oleg, i just wanted to ask if anything close to exact phrase matching could be deployed/implemented with tsearch2. > > On Tue, 13 Jun 2006, Aaron Bono wrote: > > > In another post on a different topic, Rod Taylor said the following: > > > > "A\tcat in the\nhat" might be stored as ARRAY['A', 'cat', 'in', 'the', > > 'hat']. > > > > This got me thinking. I have a discussion forum for gamers and want > > to provide searching capabilities so the user can type in a phrase > > like "magical bow" and get all posts, sorted by relevance that contain > > these words. > > > > My questions are: > > 1. Will storing the posts in an ARRAY help improve performance of > > these searches? If so, by how much? > > 2. What functions or libraries are available to make such searching > > easy to implement well? > > 3. What is the best way to sort by relevance? > > > > Thanks, > > Aaron Bono > > > > ---(end of broadcast)--- > > TIP 3: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > > > Regards, > Oleg > _ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > -- -Achilleus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Does PG have a database
O Mark Fenbers έγραψε στις May 22, 2006 : > I have working PostgreSQL databases on 3 of my ~30 Linux boxes. I want > my software to be able to determine which of my 30 boxes have functional > databases on them. Since Pg is part of the baseline distro, merely > checking for the existence of an executable doesn't solve my problem. > > I tried looping through my list of hosts and running the command: > psql -h $host --list > but this fails on a box with a database with the same error code as on a > box that doesn't have a database, if my PGUSER isn't set and my login ID > is not the username of the database. The PGUSER setting might differ > for each host, so specifying this in the software is not practical. > > Bottom line: What trick can I use to determine whether a box has a > living, breathing Pg database if I don't know the DB owner? Use a net mgmt tool (like opennms). netcat, telnet to 5432, etc... > > Mark > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > -- -Achilleus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] usernames of a group from SQL
O Kis Jαnos Tamαs έγραψε στις May 19, 2006 : > Hi, > > At the first time: I'm in newbie in SQL... I'm sorry! > > My question is: How can I get the usernames of a group (for example > 'osztatlan_users') from SQL? > > I try it: > > SELECT usename > FROM pg_user > WHERE usesysid IN (SELECT grolist FROM pg_group WHERE > groname='osztatlan_users') > ORDER BY pg_user; > > But I get the next error message: > > ERROR: operator does not exist: oid = oid[] > HINT: No operator matches the given name and argument type(s). You > may need to add explicit type casts. > > I see, what is the problem, but what can I do...? SELECT u.usename from pg_user u,pg_group g where u.usesysid = any (g.grolist) and g.groname='osztatlan_users'; > > Thans, > kjt > > > McAfee SCM 4.1 αltal ellenυrizve! > > ---(end of broadcast)---TIP > 4: Have you searched our list archives? >http://archives.postgresql.org > -- -Achilleus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Database OID
O Kis Jαnos Tamαs έγραψε στις May 17, 2006 : > Hi, > > How can I find the database OID from simple SQL command? SELECT oid,* from pg_database ; > > Thanks, > kjt > > > McAfee SCM 4.1 αltal ellenυrizve! > > ---(end of broadcast)---TIP > 6: explain analyze is your friend > -- -Achilleus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Thoughts on a Isolation/Security problem.
O Luckys έγραψε στις Apr 18, 2006 : > how about having a company table, and company_code column across all > relevant table, although you'll have to modify your application also, which > would be an additional clause in the WHERE condition e.g where company_code > = 'which company user has logged in'. > The user has to specify while logging under which company he's going to work > on. > This way would be ideal even for your Global financial consolidations if the > mgmt requires in the due course. > other option would be of two tables, Company , Organization, where you can > have company1, org1, org2 etc., this can also be applied in the same pattern > as stated above. Thanx for your thoughts, but this would require touching 173 tables + 2,594 SQL statements in a sum of 324 programs (which sum into 125,085 lines of code) > > > > On 4/18/06, Achilleus Mantzios <[EMAIL PROTECTED]> wrote: > > > > > Hi, i have run into the following problem. > > Let me describe the context first. > > > > When i joined the company(ies) i work for (a group of Shipping Mgmt/ > > Owenship/Agent companies), the only thing i was told when i started > > designing the DB/Apps was just one company. > > > > So i built everything into one single DB, and i wrote the apps > > having one company in mind. > > > > Our architecture is based on jboss3/postgresql (curenctly 7.4.12). > > There is one .ear file, which authenticates users against a lotus > > notes ldap server. > > > > At the time, the corporate organisational model was a little bit wierd: > > - Many Indepentent ownership companies > > - Many Independent Mgmg companies > > (but all busines was with one company in mind). > > > > Each App user is a member of one or more ldap groups, each group > > mapping to a mgmt company. > > > > So i ended up with > > - one DB with 173 tables > > - one DB user (postgres) > > - one .EAR application with 148,827 lines of code. > > > > Now the requirements start to change. > > The boss now bought some other types of vessels too. > > So virtually there must be N separate distinct apps, where N is the number > > of Mgmt companies (roughly one for every type of vessel), where each app > > sees and manages only its data. > > > > Moreover there are some apps that should see global data for some specific > > tables. (like the crew data, people in the crew move from one type of > > vessel to the other so they are not tied to a Mgmt company). > > > > These new requirements are of legal nature, as well as of > > operational. (People managing a type of vessels dont want to mess with > > another type, > > and auditors must see each company completely separated from the rest). > > > > Doing it with extra code would be a real pain, since i would have to > > refine > > all security/authentication based on the groups ([EMAIL PROTECTED]) > > that a person belongs to. Also this way no inherent isolation/security > > would hold. > > > > Now i am thinking of restructuring the whole architecture as: > > - Create one EAR app for every mgmt company > > - Create one DB USER for every mgmg company > > - Create one SCHEMA (same as the USER) for every mgmt company > > (mgmtcompany1,mgmtcompany2,etc...) > > - Find a way (links/xdoclet/eclipse?) to have *one* common code base for > > the N EAR apps. > > - Tweak with jboss*.xml to map java:comp/env/jdbc/DB to > > java:/pgsql, where pgsql authenticates > > with the corresponding DB USER. > > - Classify the tables into > > - The ones that apply to ALL mgmt companies (and leave them in the > > public schema) > > - The ones that apply *only* to a mgmt company and so create one under > > each SCHEMA > > - Load the data in *each* SCHEMA, except the tables that apply to all. > > - Define a process of "mgmt company"fying the tables in each schema (e.g. > > delete from mgmtcompany1.vessels the vessels that dont belong to > > mgmtcompany1, and so forth) > > - Resolve FK constraint issues > > - The default search_path in psql (whats the the equivalent in jdbc?) is > > $user,public, so effectively *each* EAR will hit automagically the correct > > mgmtcompanyN.* tables, or the public.* tables if these tables apply to all > > mgmt companies. > > > > With this way, the hard work is DB oriented, and not APP oriented. > > However i wonder whether someone else has gone thru a similar process, > > or if someone finds some assumption conceptually flawed. > > > > Thanx for reading, and for any possible thoughts. > > > > -- > > -Achilleus > > > > > > > > ---(end of broadcast)--- > > TIP 4: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > > -- > regards, Luckys... > -- -Achilleus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [JDBC] Thoughts on a Isolation/Security problem.
O Markus Schaber έγραψε στις Apr 18, 2006 : > Hi, Achilleus, > > Achilleus Mantzios wrote: > > > Now i am thinking of restructuring the whole architecture as: > > - Create one EAR app for every mgmt company > > - Create one DB USER for every mgmg company > > - Create one SCHEMA (same as the USER) for every mgmt company > > (mgmtcompany1,mgmtcompany2,etc...) > > We're doing a very similar thing here for one of our legacy apps, which > luckily does not know anything about schemas, and so the search_path > trick does work. > > However, for most "global" tables we have views with insert/update/ > delete rules in the specific schemas, and such shield the application > from directly accessing the global data. We even need to mere local and > global data this way in some cases. > > It is ugly, but it works fine and is manageable. If no exotic/contrib code is to be used then i think splitting into separate Schemas (versus separate DBs) will make future consolidation/stats/accounting (global data) code easy to write. (Unless ofcourse some real cross-db sql join features appear which is not the case at the moment). Why do you think its ugly after all? > > HTH, > Markus > -- -Achilleus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Thoughts on a Isolation/Security problem.
Hi, i have run into the following problem. Let me describe the context first. When i joined the company(ies) i work for (a group of Shipping Mgmt/ Owenship/Agent companies), the only thing i was told when i started designing the DB/Apps was just one company. So i built everything into one single DB, and i wrote the apps having one company in mind. Our architecture is based on jboss3/postgresql (curenctly 7.4.12). There is one .ear file, which authenticates users against a lotus notes ldap server. At the time, the corporate organisational model was a little bit wierd: - Many Indepentent ownership companies - Many Independent Mgmg companies (but all busines was with one company in mind). Each App user is a member of one or more ldap groups, each group mapping to a mgmt company. So i ended up with - one DB with 173 tables - one DB user (postgres) - one .EAR application with 148,827 lines of code. Now the requirements start to change. The boss now bought some other types of vessels too. So virtually there must be N separate distinct apps, where N is the number of Mgmt companies (roughly one for every type of vessel), where each app sees and manages only its data. Moreover there are some apps that should see global data for some specific tables. (like the crew data, people in the crew move from one type of vessel to the other so they are not tied to a Mgmt company). These new requirements are of legal nature, as well as of operational. (People managing a type of vessels dont want to mess with another type, and auditors must see each company completely separated from the rest). Doing it with extra code would be a real pain, since i would have to refine all security/authentication based on the groups ([EMAIL PROTECTED]) that a person belongs to. Also this way no inherent isolation/security would hold. Now i am thinking of restructuring the whole architecture as: - Create one EAR app for every mgmt company - Create one DB USER for every mgmg company - Create one SCHEMA (same as the USER) for every mgmt company (mgmtcompany1,mgmtcompany2,etc...) - Find a way (links/xdoclet/eclipse?) to have *one* common code base for the N EAR apps. - Tweak with jboss*.xml to map java:comp/env/jdbc/DB to java:/pgsql, where pgsql authenticates with the corresponding DB USER. - Classify the tables into - The ones that apply to ALL mgmt companies (and leave them in the public schema) - The ones that apply *only* to a mgmt company and so create one under each SCHEMA - Load the data in *each* SCHEMA, except the tables that apply to all. - Define a process of "mgmt company"fying the tables in each schema (e.g. delete from mgmtcompany1.vessels the vessels that dont belong to mgmtcompany1, and so forth) - Resolve FK constraint issues - The default search_path in psql (whats the the equivalent in jdbc?) is $user,public, so effectively *each* EAR will hit automagically the correct mgmtcompanyN.* tables, or the public.* tables if these tables apply to all mgmt companies. With this way, the hard work is DB oriented, and not APP oriented. However i wonder whether someone else has gone thru a similar process, or if someone finds some assumption conceptually flawed. Thanx for reading, and for any possible thoughts. -- -Achilleus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Ugly group by problem
O Markus Schaber έγραψε στις Mar 29, 2006 :
> Hi, Achilleus,
>
> Achilleus Mantzios wrote:
>
> > foodb=# SELECT qoo.foo2,sum(qoo.foo3) from (SELECT mt.link_id as
> > foo,_int_union(array(select mt2.feat_id from markustest mt2 where
> > mt2.link_id=mt.link_id order by mt2.feat_id),'{}') as foo2,other::int4 as
> > foo3 from markustest mt) as qoo GROUP BY qoo.foo2;
> > foo2 | sum
> > +-
> > {2}| 1
> > {5,23} | 13
> > {23} | 14
> > (3 rows)
>
> This is much like I intended to do it, but using "select distinct" in
> the inner select as I don't have _int_union here, and using a temporary
> table to collect the sets of link ids.
Just some facts:
sort(uniq(int[])) has the same effect as select DISTINCT... ORDER BY
DISTINCT. ORDER BY is ofcourse more intuitive and does not need
contrib/intarray.
In fact , i dont even remember why i didnt write the query with
DISTINCT... in the first place :)
>
> Markus
>
>
>
--
-Achilleus
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Flight numbers data
O Scott Marlowe έγραψε στις Mar 29, 2006 : > On Wed, 2006-03-29 at 02:17, Achilleus Mantzios wrote: > > Hi, i am in the process of writing an application about > > tickets, flights, etc, and i am thinking of getting the primitive > > data ready at the begining and doing it the right way, > > (e.g. the user will just select a flight number and doesnt > > have to enter the airports, airport coordinates, countries, > > airline name, airline code, departure/arrival times, etc...) > > > > I found some sources on the web about airlines (codes, names, countries, > > etc...), also i found about airports, but nothing about flight numbers! > > That's cause companies that keep track of those things charge a LOT of > money for their datasets. > > > This application will be for enterprise (internal) company use, > > covering the flights of emploees around the world. > > > > I was just wondering how internet ticket agents manage to do it. > > They subscribe to some service that has this information in the back > end. The data in that back end comes from the one or two sources of > that data who charge yearly subscriptions in the hundreds of thousands > of dollars. Just to examine a little closer (bassically show my boss this!), can you give me some pointers of some kind of web services providers (in the broad sense) that sell such yearly subscriptions, for internet travel agents? > > > Has anyone from the postgresql camp ever solved such a problem? > > Where I work, we're building a middle level system (look up the website > that goes with my domain). And if we weren't in the airline reservation > industry, we couldn't afford the data sets. > > > It is just that i wanna do it right, maybe play a little bit with AJAX > > also who knows :) > > But "doing it right" goes against almost every tenet of the airline > reservation industry :) haha. only serious. > > Seriously though, you might be able to get your travel agent or whoever > you do reservations through to provide you with this information in some > kind of reliable format for the tickets you've bought. If you can get > some kind of automated feed from them, that would be the best way, if > not, maybe they can email you the info each time, and you can cut and > paste it in. > > There's much in the reservation industry that is basically a computer > implementation of a 3x5 note card system. And none of those systems > were originally built to talk to each other, so it's often impossible > for a single user to get any kind of automatically created data from > such an industry. That was pretty much what i suspected, i have seen some 3270 type terminal emulators in some travel agents that reminded me VSE/MVS/AS400/CICS and other beauties like that!! Well, Scott your response was really helpful. What i will do is just build the tables with airlines info, airports info (IATA codes,etc..) with sources for the web. Really are there any good sources out there for such info? For airports i came across http://www.partow.net/miscellaneous/airportdatabase/ it seems very complete but it misses new airports (it doesnt have the new Greek one (IATA code ATH)which is some 5 years old). Other sources i found is just HTML pages which is not so handy. Then i would let the in-company tickets person build the flight numbers table incrementally as she works. What do you think? > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > -- -Achilleus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Ugly group by problem
O Markus Schaber έγραψε στις Mar 29, 2006 :
> Hello,
>
> I have a table filled from third party that basically has the following
> structure:
>
> link_id | feat_id | other | columns...
> +-+---+---
> 1 | 2 | ...
> 2 | 5 | ...
> 2 | 23 | ...
> 3 | 5 | ...
> 3 | 23 | some | data
> 3 | 23 | other | data
> 5 | 23 | ...
> 9 | 23 | ...
>
> This structure is fixed, and we can't change it, but we can create
>
> We have about 37 million different link_ids, and 35 million feat_ids.
> There are feat_ids that appear at several thousand link_ids, but a
> link_id does not have more than a douzen feat_ids.
>
> Now I need to group together all link_ids that have the same set of
> feat_ids. In the example above, the sets would be (1), (2,3) and (5,9),
> and the other columns would be run through some aggregate functions.
>
> Currently, this is done via an external JAVA application, but I'm
> looking for a way to express this via sql / plpgsql to ease deployment.
>
> I could imagine some ugly code using ARRAY (not tried yet), but how
> would you pack this problem? It seems that I'm just stuck in my thoughts
> and miss the beauty way to solve it.
Well i did it with arrays, i dont know the performance
implications tho:
foodb=# SELECT * from markustest ;
link_id | feat_id | other
-+-+---
1 | 2 | 1
2 | 5 | 2
2 | 23 | 2
3 | 5 | 3
3 | 23 | 3
3 | 23 | 3
5 | 23 | 5
9 | 23 | 9
(8 rows)
foodb=# SELECT qoo.foo2,sum(qoo.foo3) from (SELECT mt.link_id as
foo,_int_union(array(select mt2.feat_id from markustest mt2 where
mt2.link_id=mt.link_id order by mt2.feat_id),'{}') as foo2,other::int4 as
foo3 from markustest mt) as qoo GROUP BY qoo.foo2;
foo2 | sum
+-
{2}| 1
{5,23} | 13
{23} | 14
(3 rows)
foodb=#
The _int_union trick is to force the arrays to have unique values.
The order by has the meaning that '{5,23}' and '{23,5}' should be treated
the same way.
>
> Thanks,
> Markus
>
--
-Achilleus
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] Flight numbers data
Hi, i am in the process of writing an application about tickets, flights, etc, and i am thinking of getting the primitive data ready at the begining and doing it the right way, (e.g. the user will just select a flight number and doesnt have to enter the airports, airport coordinates, countries, airline name, airline code, departure/arrival times, etc...) I found some sources on the web about airlines (codes, names, countries, etc...), also i found about airports, but nothing about flight numbers! This application will be for enterprise (internal) company use, covering the flights of emploees around the world. I was just wondering how internet ticket agents manage to do it. Has anyone from the postgresql camp ever solved such a problem? One point for me, is that my company is doing shipping, and we are not in the travel agents business (IATA, standards etc..) at all, so its hard for me to find the path to follow, and in addition, going the full Travel Agent path would be an overkill, since what we need is just an application for the tickets. It is just that i wanna do it right, maybe play a little bit with AJAX also who knows :) Thanx for any hints. -- -Achilleus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Proposed dbmirror change
hi again
i made one mod to dbmirror.
1) I implemented a mechanism to exclude attributes (columns) from
dbmirror'ed tables.
I added one table
CREATE TABLE dbmirror_exclude_attributes (
tblname character varying(100) NOT NULL,
attnames text[] NOT NULL
);
ALTER TABLE ONLY dbmirror_exclude_attributes
ADD CONSTRAINT dbmirror_exclude_attributes_pkey PRIMARY KEY (tblname);
and one function
bool isExcluded(char *cpTableName,TupleDesc tTupleDesc, int iColumnCounter);
which is called in packageData, and examines to see if this
column should be excluded.
The contents of dbmirror_exclude_attributes are like:
dynacom=# SELECT * from dbmirror_exclude_attributes;
tblname | attnames
--+---
"public"."mariner" | {parentid,relationtypeid}
"public"."marinerpapers" | {mpaid}
(2 rows)
dynacom=#
One use of it would be the selective mirroring of only a subset of
all of one table's columns, excluding e.g. huge bytea columns,
where the communication link is for instance ultra expensive and
unreliable SAT connection.
Another use case would be the exclusion of sensitive information
like credit card numbers or medical data.
What do you all think?
-Achilleus
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] COPY tablename FROM and null values
O ivan marchesini έγραψε στις Mar 22, 2006 : > Dear users, > I'm working on a Postgres 7.4 server > > I have a .txt file, containing some tabular data, where data are > delimited by TABs. > there are 3 columns: > > column1 int4, column2 float8, column3 float8 > > the problem is that column3 contains also null values (i.e. sometimes is > empty) > > so when I try to use COPY tablename FROM 'filename.txt' I obtain an > error > > I have tried also using " WITH NULL AS ' ' " but without good results... > > can someone explain me how to solve this problem??? batch edit your file (with sed,awk,perl,C,java,...) and build your explicit INSERT statements in some version of your file. > > thank you very much > > Ivan > > > > > -- -Achilleus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Job queue, how would you implement this?
O Alexis Paul Bertolini έγραψε στις Mar 21, 2006 : > Dear all, > > I have implemented a job queue table where various apps can add jobs to > the queue and other daemons then execute them. A basic producer-consumer > pattern. Each tuple in the queue has the basic info such as job to be > done, when it should be done, who should do it, a flag marking it completed. > > The consumer thus selects from the queue all jobs who scheduled in the > past (<=CURRENT_TIMESTAMP) and are not flagged. Then flags them upon > succesfull completion. The consumer runs from the command line? (i.e. user-action driven?) > > I now wish to implement repeating scheduled jobs, like "every 5 > minutes", "every morning at 7.00am" and so on. How could I do this? I think the traditional UNIX "at" framework could be a good model (or replacement) of the simpler case, whereas the traditional UNIX "cron" framework could be a good model (or replacement) of the latter case. Your requirements photographically point to the above systems. Are you running on a UNIX machine? > > Any suggestions are welcome and if necessary I can provide you with more > info. > > Thanks > > Alex. > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > -- -Achilleus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] have you feel anything when you read this ?
O Eugene E. έγραψε στις Mar 20, 2006 :
> Peter Eisentraut wrote:
> > Eugene E. wrote:
> >
> >>the bytea does not output NULs at all.
> >>don't mock me.
> >
> >
> > peter=# create table test (a bytea);
> > CREATE TABLE
> > peter=# insert into test values ('a\\000b');
> > INSERT 0 1
> > peter=# select * from test;
> >a
> >
> > a\000b
>
> are you kidding ?
> where is NUL-byte in "a\000b" ???
Null byte is a byte of value zero,
and allow me to say that the \000 in "a\000b" is exactly this.
A NULL value is commonly used in C to terminate a pointer's data.
>
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
>
--
-Achilleus
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] have you feel anything when you read this ?
O Alvaro Herrera έγραψε στις Mar 20, 2006 : > Achilleus Mantzios wrote: > > > dynacom=# SELECT '\000\150\145\154\154\157'::text; > > text > > -- > > > > (1 row) > > > > dynacom=# > > > > Oops! > > text is not bytea. > > alvherre=# SELECT $$\000\150\145\154\154\157$$::bytea; >bytea > --- > \000hello > (1 fila) Sure, but we are trying to reproduce the mysql phaenomenon right? :) > > -- -Achilleus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] have you feel anything when you read this ?
O Peter Eisentraut έγραψε στις Mar 20, 2006 :
> Eugene E. wrote:
> > the bytea does not output NULs at all.
> > don't mock me.
>
> peter=# create table test (a bytea);
> CREATE TABLE
> peter=# insert into test values ('a\\000b');
> INSERT 0 1
> peter=# select * from test;
>a
>
> a\000b
Just did
dynacom=# SELECT '\150\145\154\154\157'::text;
text
---
hello
(1 row)
dynacom=#
dynacom=#
dynacom=# SELECT '\000\150\145\154\154\157'::text;
text
--
(1 row)
dynacom=#
Oops!
>
>
--
-Achilleus
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: R: Re: R: R: Re: [SQL] schema inspection
O [EMAIL PROTECTED] έγραψε στις Mar 17, 2006 : > > > >SELECT c1.relname,c2.relname from pg_constraint cons,pg_class c1, > pg_class > >c2 where cons.conrelid=c1.oid and cons.confrelid = c2.oid; > > > >for column(s) names you will have to do extra homework. > > Thanks! I have obtained my query! Here is: > > SELECT > (SELECT relname FROM pg_catalog.pg_class WHERE oid=conrelid) AS > fromTbl, > (SELECT relname FROM pg_catalog.pg_class WHERE oid=confrelid) AS > toTbl, > (SELECT attname FROM pg_catalog.pg_attribute WHERE attrelid=conrelid > AND conkey[1]=attnum) AS viaCol > FROM pg_catalog.pg_constraint AS rel WHERE contype='f'; Well thats it if you use only *single column* Foreign keys. In the general case the above will need extra work. Of course you will also have to ensure that the constraint is indeed a FK constraint, that the column is not droped, etc which leads to the answer that enabling statement logging, and then \d and watching the log is a very good friend too. > > TIA > Roberto Colmegna > > > > > Tiscali ADSL 4 Mega Flat > Naviga senza limiti con l'unica Adsl a 4 Mega di velocitΓ a soli 19,95 β¬ > al mese! > Attivala subito e hai GRATIS 2 MESI e l'ATTIVAZIONE. > http://abbonati.tiscali.it/banner/middlepagetracking.html?c=webmailadsl&r=http://abbonati.tiscali.it/adsl/sa/4flat_tc/&a=webmail&z=webmail&t=14 > -- -Achilleus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: R: R: Re: [SQL] schema inspection
O [EMAIL PROTECTED] έγραψε στις Mar 16, 2006 : > > pg_catalog.pg_constraint is your (only?) friend. > > I have already examintated this table without results. Seem not to be > a "human-readable" table :( Right you will have to join against pg_class, and make it readable. SELECT c1.relname,c2.relname from pg_constraint cons,pg_class c1, pg_class c2 where cons.conrelid=c1.oid and cons.confrelid = c2.oid; for column(s) names you will have to do extra homework. > > TIA > Roberto Colmegna > > > > > > Tiscali ADSL 4 Mega Flat > Naviga senza limiti con l'unica Adsl a 4 Mega di velocitΓ a soli 19,95 β¬ > al mese! > Attivala subito e hai GRATIS 2 MESI e l'ATTIVAZIONE. > http://abbonati.tiscali.it/banner/middlepagetracking.html?c=webmailadsl&r=http://abbonati.tiscali.it/adsl/sa/4flat_tc/&a=webmail&z=webmail&t=14 > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > -- -Achilleus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] schema inspection
O [EMAIL PROTECTED] έγραψε στις Mar 16, 2006 : > Hi, > > supposing to have a small DB: > > TABLE a ( > id SERIAL PRIMARY KEY > ); > > TABLE b ( > id SERIAL PRIMARY KEY, > idA INTEGER NOT NULL REFERENCES a(id) > ); > > How can I inspect pg_schema/information_schema to "detect" the > relation between "b" and "a" via "idB"? > pg_catalog.pg_constraint is your (only?) friend. > TIA > Roberto Colmegna > > > > > > Tiscali ADSL 4 Mega Flat > Naviga senza limiti con l'unica Adsl a 4 Mega di velocitΓ a soli 19,95 β¬ > al mese! > Attivala subito e hai GRATIS 2 MESI e l'ATTIVAZIONE. > http://abbonati.tiscali.it/banner/middlepagetracking.html?c=webmailadsl&r=http://abbonati.tiscali.it/adsl/sa/4flat_tc/&a=webmail&z=webmail&t=14 > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > -- -Achilleus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] connectby documentation
O Jean-Paul Argudo έγραψε στις Mar 14, 2006 :
> Daniel Caune a ιcrit :
> > Wow, that was the quest for the Holy Grail! :-)
>
> Yes I understand. That kind of documentation for a
> contrib-addon-whatever for PostgreSQL can be tricky sometimes to find..
>
>
> I just jump on that thread to place a reminder for all those wanting to
> implement trees in databases, just in case they are still thinking about
> howto do that.
>
> I wroted an article on that topic (in french only sorry :
> http://www.postgresqlfr.org/?q=node/142), where I compare id/parent_id,
> nested loops and Miguel Sofer's method.
>
> This method is explained on OpenACS forums (in english)
>
> http://openacs.org/forums/message-view?message_id=18365
>
> The original work of Miguel Sofer (with a PostgreSQL implementation as
> an example) can be found here:
>
> http://www.utdt.edu/~mig/sql-trees/
>
> Be sure to download the tar.gz. on the like "here"... and read his draft.
>
> I'm really convinced this method is the best so far. I used it in 3
> different projects where I had to implement big trees structures on a
> table. They all still work with no problem of any kind.
I agree, this genealogical approach is i think the most
intuitive/efficient, however this depends on the nature
of the intented operation types.
One implementation of this (i think) is the ltree contrib module.
Haven't worked with this tho.
What i actually did for my ultra demanding task (modeling inventory
maintenance of 709772 machinery items/parts etc... of ~ 40 vessels), was
smth of the type
defid | integer| not null default
nextval('public.machdefs_defid_seq'::text)
parents | integer[] |
description | text |
machtypeid | integer
..
where parents hold the path from the item's direct parent to its root
ancestor,
and tree queries are done with a help of a intarray index on parents
"machdefs_parents" gist (parents gist__intbig_ops)
>
> Just to let you know in case you missed that ;-)
>
> My 2 ’
>
> --
> Jean-Paul Argudo
> www.Argudo.org
> www.PostgreSQLFr.org
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org
>
--
-Achilleus
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] removing "not null" modifier
O Robert Urban έγραψε στις Mar 13, 2006 :
> Hello,
>
> let's say I have created a postgresql-7.2.2 db using the following cmds:
>
> CREATE TABLE status
> (
> id SERIAL NOT NULL PRIMARY KEY,
> name VARCHAR(32)
> );
>
> CREATE TABLE event
> (
> id SERIAL NOT NULL PRIMARY KEY,
> description VARCHAR(32),
> status_id INTEGER NOT NULL REFERENCES status(id)
> );
>
> in psql I then see:
>
> mydb=# \d event
> Table "event"
>Column| Type |Modifiers
>
> -+---+--
> id | integer | not null default
> nextval('"event_id_seq"'::text)
> description | character varying(32) |
> status_id | integer | not null
> Primary key: event_pkey
> Triggers: RI_ConstraintTrigger_43210
>
>
> The question:
>
> how can I get rid of the "not null" modifier on status_id?
ALTER TABLE event ALTER status_id DROP NOT NULL;
>
> thanks,
>
> Robert Urban
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>
--
-Achilleus
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] After Trigger assignment to NEW
O Owen Jacobson έγραψε στις Feb 24, 2006 : > Achilleus Mantzios wrote: > > > O Tom Lane έγραψε στις Feb 24, 2006 : > > > > > By definition, an AFTER trigger is too late to change what was > > > stored. Use a BEFORE trigger. > > > > Too late if someone wants to store it. > > I wanna store the intented original values, thats why i use > > AFTER trigger. > > But i would like to alter what a final AFTER trigger would see. > > > > I'll elabarote a little. > > > > An update happens. > > The row is stored. > > An after trigger is fired that alters some NEW columns > > (nullifies them), aiming for a subsequent trigger > > to see the altered results . > > > > It should be something like a pointer to a HeapTuple, (right?), > > so that would be feasible i suppose. > > > > I would not even make a post if it was something that trivial. > > > > I hope you get my point. > > Your real problem is that the "subsequent" trigger has behaviour you don't > like. That's what you should be fixing. If dbmirror has no way to exclude > specific tables from mirroring, take it up with them as a feature request, or > patch dbmirror to work how you want it to. > > AFTER triggers *must* receive the row that was actually > inserted/updated/deleted. If they could receive a "modified" row that didn't > reflect what was actually in the database, all sorts of useful trigger-based > logging and replication patterns wouldn't work, and there's really no other > way to implement them. See also Tom Lane's other message for further > implications of being able to modify the rows seen by AFTER triggers. > As i have explained my dbmirror is FK null values gnostic(=aware) already as we speak. It normaly mirrors father rows according to certain criteria. (And the fathers of them and so on). Replication is done over UUCP over 5$/min satelite connections, so replicating just the right data for a slave is critically important. So nullifying a value just before the dbmirror trigger would do exactly the right thing (for me) Now implementing the "nullification on demand" feature in dbmirror means more work when i migrate to 8.x, i have severly modified dbmirror to do many things, and i thought it was time to stop! > I'd also be hesitant to write triggers that have to execute in a specific > order. Meaning that would hurt portability? Most people need features rathen than the relief to know they can migrate to another database (which they probably never will) > Back to AFTER trigger changing values issue, i think things are not so dramatic if FK triggers could just be fired first. Anyway i'll modify dbmirror again. Oh BTW, There is a patch for DBMirror.pl (which steven hasnt yet fully reviewed) that solves the previous performance problems. > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > -- -Achilleus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] After Trigger assignment to NEW
O Tom Lane έγραψε στις Feb 24, 2006 : > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> Achilleus Mantzios <[EMAIL PROTECTED]> writes: > >>> Is there a reason that the NEW values should remain unchanged in AFTER > >>> row triggers? > >> > >> By definition, an AFTER trigger is too late to change what was stored. > >> Use a BEFORE trigger. > > > But a BEFORE trigger would alter the stored tuple, which is not what > > Achilleus wants AFAIU. > > Oh, I misunderstood what he wanted ... and now that I do understand, > I think it's a really terrible idea :-(. A large part of the point > of using an AFTER trigger is to be certain you know exactly what got > stored. (BEFORE triggers can never know this with certainty because > there might be another BEFORE trigger that runs after them and edits the > tuple some more.) If one AFTER trigger could falsify the data seen by > the next, then that guarantee crumbles. For instance, a minor > programming error in a user-written trigger could break foreign-key > checking. No thanks. Alvaro, Tom, thanx a lot, i'll have to incorporate that into dbmirror. > > > I think the desired effect can be had by having DBMirror check the > > source relation of the inserted tuple (There is a hidden attributa > > called tableoid IIRC that can be used for that, I think). > > I agree --- the correct solution is to change the DBMirror triggers to > incorporate the desired filtering logic. > > regards, tom lane > -- -Achilleus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] After Trigger assignment to NEW
O Tom Lane έγραψε στις Feb 24, 2006 : > Achilleus Mantzios <[EMAIL PROTECTED]> writes: > > Is there a reason that the NEW values should remain unchanged in AFTER > > row triggers? > > By definition, an AFTER trigger is too late to change what was stored. > Use a BEFORE trigger. Too late if someone wants to store it. I wanna store the intented original values, thats why i use AFTER trigger. But i would like to alter what a final AFTER trigger would see. I'll elabarote a little. An update happens. The row is stored. An after trigger is fired that alters some NEW columns (nullifies them), aiming for a subsequent trigger to see the altered results . It should be something like a pointer to a HeapTuple, (right?), so that would be feasible i suppose. I would not even make a post if it was something that trivial. I hope you get my point. > > regards, tom lane > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match > -- -Achilleus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] After Trigger assignment to NEW
Hi, i am currently on 7.4.12, and i what i try to do is having an AFTER row trigger nullify one NEW column, so as to prevent a subsequent (AFTER row) trigger from using this column's data. Unfortunately this nullification (assignment) does not have any effect on the next AFTER trigger. The first trigger is in pl/pgsql, and the second is in C. One possible use of the above (if it worked) would be the following. Suppose the next AFTER triger is the (enhanced version of) dbmirror trigger which takes care of FK constraints and navigates thru the graph in order to mirror all depenent tables' rows too. Suppose there is no need to mirror a specific parent table. Then in the child table i write an AFTER trigger that nullifies this columns which is FK to the said parent table, and so prevent the unwanted traversal from happening in the execution of the next AFTER trigger (dbmirror). I'd like to ask, if it is considered the right behaviour and if there is a plan in changing it. Is there a reason that the NEW values should remain unchanged in AFTER row triggers? -- -Achilleus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Feature, my misconception or bug??
O Markus Schaber έγραψε στις Feb 22, 2006 : > Achilleus Mantzios wrote: > > In PgSQL 7.4.12, > > > > foodb=# SELECT '255.255.255.255/24'::cidr; > > ERROR: invalid cidr value: "255.255.255.255/24" > > DETAIL: Value has bits set to right of mask. > > foodb=# > > > > SELECT '255.255.255.255/25'::cidr; > > cidr > > > > 255.255.255.255/25 > > (1 row) > > This one is refused in 8.1, so I guess that's a fixed bug. Thanx Marcus, it appears to have been fixed in http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/network.c.diff?r1=1.53;r2=1.54;f=h > > HTH, > Markus > > > -- -Achilleus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Feature, my misconception or bug??
O Peter Eisentraut έγραψε στις Feb 22, 2006 : > Am Mittwoch, 22. Februar 2006 13:04 schrieb Achilleus Mantzios: > > foodb=# SELECT '255.255.255.255/24'::cidr; > > ERROR: invalid cidr value: "255.255.255.255/24" > > DETAIL: Value has bits set to right of mask. > > > in this case > > ... > > has no bits set to right of > > 8 LSB ^ > In the above statement there was a "?", which is missing here. It should read: "in this case ... has no bits set to right of 8th Least Significant Bit???" (with a ^ showing the position where the mask ends, making it obviously visible to the ones who use text base MUAs in fixed width font terminals). In other words, why doesnt the system produce an error for the second query as well??? > I'm sure you are aware that "1" is a set bit, so which part are you not > understanding? Others (Marcus) have reported that the second query SELECT '255.255.255.255/25'::cidr; indeed produces an error in 8.1; so i guess its a bug in the 7.4.x series. > > -- -Achilleus ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Feature, my misconception or bug??
In PgSQL 7.4.12, foodb=# SELECT '255.255.255.255/24'::cidr; ERROR: invalid cidr value: "255.255.255.255/24" DETAIL: Value has bits set to right of mask. foodb=# SELECT '255.255.255.255/25'::cidr; cidr 255.255.255.255/25 (1 row) foodb=# in this case ... has no bits set to right of 8 LSB ^ ?? -- -Achilleus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Avoid querying a specific field - how?
O Ibrahim Tekin έγραψε στις Feb 21, 2006 : > i may i misunderstood your question but it seems to me, partial indexes will > work for you. > check this example: > http://www.postgresql.org/docs/8.1/interactive/indexes-partial.html#INDEXES-PARTIAL-EX2 > > On 2/21/06, Stefan Schwarzer <[EMAIL PROTECTED]> wrote: > > > > Hi, > > > > does anyone know how to build a query that SELECTs all fields but not > > a specific one? I have a couple of tables, holding statistical > > information as well as the geospatial one (via Postgis). When I query > > the table I'd like to query only the statistical part - not the > > geospatial, which is not necessary and slows done incredibly the > > query. I searched a bit within the Postgres SQL domain. It seems > > there is an EXCEPT statement, but it seems that it works differently... Why dont you exclude the geospatial fields?? If * is a must then build a view. > > > > Thanks for any help, > > > > Stef > > > > ---(end of broadcast)--- > > TIP 1: if posting/reading through Usenet, please send an appropriate > >subscribe-nomail command to [EMAIL PROTECTED] so that your > >message can get through to the mailing list cleanly > > > -- -Achilleus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] passing array to database function
O Richard Huxton έγραψε στις Feb 7, 2006 :
> padmanabha konkodi wrote:
> > Β hi everybody,
> >
> > i want to pass Integer array to database function from java
> > precompiled statement.
> >
> > java gives java.sql.Array interface but i dont know which
> > implementing class i have to use in postgre
>
> I don't really "do" java, but have you tried an array of integers for
> your PostgreSQL function? What happened?
The correct list is -jdbc.
Theoritically you would have to write your own implementation
of java.sql.Array.
Practically build a String with the contents you want
e.g.
'{1,2,3,4}'
for a int[] array, and your done.
>
>
--
-Achilleus
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Does PostgreSQL support job?
O Markus Schaber έγραψε στις Feb 2, 2006 : > H, Achilleus, > > Achilleus Mantzios wrote: > > >>PLPGSQL is turing complete, plain SQL is not. > > H is SQL equally powerful as a pushdown automaton then??? > > SQL is _not_ a programming language, it is a query language. It is not > meant to be turing complete. > > Just as e. G. HTML, CSS or RFC2822 are structural or layout languages, > but not programming languages. > > > Just kidding! > > Now, you're kidding. :-) Well, if we add one stack to SQL it will kick some major PASCAL ass! > > HTH, > Markus > -- -Achilleus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Does PostgreSQL support job?
O Markus Schaber έγραψε στις Feb 2, 2006 : > Hi, Daniel, > > Daniel Caune wrote: > > > I'm not sure to understand. Why calling a function from a script is > > different from executing a series of SQL commands? I mean, I can run a > > script defined as follows: > > > > SELECT myjob(); > > > > where myjob is a stored procedure such as: > > > > CREATE OR REPLACE FUNCTION myjob() > > RETURNS void > > AS $$ > > > > END; > > $$ LANGUAGE PLPGSQL; > > > > Does that make sense? > > It does make sense if myjob() does more than just execute a bunch of > statements, e. G. it contains if(), loops or something else. > > PLPGSQL is turing complete, plain SQL is not. H is SQL equally powerful as a pushdown automaton then??? Just kidding! > > Markus > -- -Achilleus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] how is searchable email archive on
O Alvaro Herrera έγραψε στις Jan 31, 2006 : > Jacob Costello wrote: > > > Has anyone tried using a postgres based solution for archiving email for > > regulatory purposes ? > > I don't know about regulatory purposes, but the Oryx software is > designed for that: http://www.oryx.com (or .net, don't remember) Yukatan data model + utility is also a very good and comprehensive way to model RFC 2822 mail messages. > > -- -Achilleus ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] DBMirror.pl performance change
Steven Hi,
i hope you are ok.
I discovered a problem in DBMirror.pl, performance wise.
pending.c stores data in a way
very similar to the PgSQL input "\" escaped format.
When the field is of type bytea, and the source of data is binary, then
this produces 2 additional backslashes for every unprintable
char.
The performance in function extractData in DBMirror.pl, really suffers
from this condition, since it breaks data in chunks of "\" delimited
strings.
Informally speaking, performance tends to be O(n) where n is the size
of the data.
This can be remedied if we break data in chunks of "'" rather than "\".
"'" happens much more infrequently in common binary files (bz2, tiff, jpg,
pdf etc..), and if we notice that odd number of contained "\", signals an
intermidiate "'", whereas even number of "\" signals the final "'",
then we can make this routine run much faster.
I attach the new extractData function.
Please feel free for any comments.
--
-Achilleus
sub extractData($$) {
my $pendingResult = $_[0];
my $currentTuple = $_[1];
my $fnumber;
my %valuesHash;
$fnumber = 4;
my $dataField = $pendingResult->getvalue($currentTuple,$fnumber);
my $numofbs;
while(length($dataField)>0) {
# Extract the field name that is surronded by double quotes
$dataField =~ m/(\".*?\")/s;
my $fieldName = $1;
$dataField = substr $dataField ,length($fieldName);
$fieldName =~ s/\"//g; #Remove the surronding " signs.
if($dataField =~ m/(^= )/s) {
#Matched null
$dataField = substr $dataField , length($1);
$valuesHash{$fieldName}=undef;
}
elsif ($dataField =~ m/(^=\')/s) {
#Has data.
my $value;
$dataField = substr $dataField ,2; #Skip the ='
LOOP: { #This is to allow us to use last from a do loop.
#Recommended in perlsyn manpage.
do {
my $matchString;
my $matchString2;
#Find the substring ending with the first ' or first \
$dataField =~ m/(.*?[\'])?/s;
$matchString = $1;
$numofbs = ($matchString =~ tr/\\//) % 2;
if ($numofbs == 1) { #// odd number of \, i.e. intermediate '
$matchString2 = substr $matchString,0, length($matchString)-2;
$matchString2 =~ s//\\/g;
$value .= ($matchString2 . "\'");
$dataField = substr $dataField,length($matchString);
}
else { #// even number of \, i.e. found end of data
$matchString2 = substr $matchString,0, length($matchString)-1;
$matchString2 =~ s//\\/g;
$value .= $matchString2;
$dataField = substr $dataField,length($matchString)+1;
last;
}
} until(length($dataField)==0);
}
$valuesHash{$fieldName} = $value;
}#else if
else {
logErrorMessage "Error in PendingData Sequence Id " .
$pendingResult->getvalue($currentTuple,0);
die;
}
} #while
return %valuesHash;
}
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Still struggling with history tables
O Ken Winter έγραψε στις Jan 17, 2006 :
> Friends ~
>
> I'm still trying to implement a solution to the requirement to keep a
> complete history of data changes to a "person" table. (See earlier
> correspondence below.) I'm trying for a variant of the architecture
> suggested by Richard Huxton (also below). In my variant, I have this
> "identifier" table, carrying the id and invariant info about each person:
>
> /*==*/
> /* Table: person_i */
> /*==*/
> create table person_i (
> idi BIGSERIALnot null,
> date_of_birthDATE null,
> constraint PK_person_i_key_1 primary key (idi)
> )
> ;
>
> And then I have this "history" table, carrying the time-varying info on
> which I want to keep a complete history:
>
> /*==*/
> /* Table: person_h */
> /*==*/
> create table person_h (
> idh INT8 not null,
> start_date DATE not null default 'now()',
> end_date DATE null,
> name VARCHAR(255) null,
> constraint PK_person_h_key_1 primary key (idh, start_date),
> constraint fk_reference_6 foreign key (idh)
>references person_i (idi)
> on delete restrict on update restrict
> )
> ;
>
> Triggers are in place on the "person_h" table so that when an app does an
> update, the current h record is expired (with its old data) and a new record
> (wuth the updated data)is inserted and made effective "now". What I'm now
> trying to build is this view:
>
> /*==*/
> /* View: person */
> /*==*/
> create view person as
> select idi, date_of_birth, start_date, end_date, name
> from person_i i, person_h h
> where i.idi = h.idh;
>
> I want to enable users (and apps) who need to deal only with current data to
> be able to treat "person" as a real table (i.e. to write to it as well as
> read from it). Specifically, the requirements are that when a user does:
>
> . Insert - The system inserts a record into the i table and the first
> record in the h table.
> . Select - The system returns attributes of i and h tables (not
> duplicating the identifier columns).
> . Update - The system allows updating of i attributes
> (update-in-place, not creating a new history record) and h attributes
> (creating a new history record).
> . Delete - The system deletes the i record and all of its h records.
>
> I'm stuck on how to implement the "insert" action, which I thought would be
> simple. The problem is this: The i table id is of type BIGSERIAL, i.e.
> sequence-assigned. I've tried writing the following rule to get both the i
> record and the first h record inserted:
>
> CREATE RULE ru AS
> ON INSERT TO person
> DO INSTEAD (
> INSERT INTO person_i DEFAULT VALUES;
> INSERT INTO person_h (idh) VALUES (NEW.idi)
> );
How about
INSERT INTO person_i (idi,dateofbirth)
VALUES(nextval('public.person_i_idi_seq'::text),new.dateofbirth);
INSERT INTO person_h(idh)
VALUES(currval('public.person_i_idi_seq'::text));
However, i have treated similar problems with two different approaches:
Generally it is very hard to distinguish between two kind of UPDATES:
a) UPDATEs that mean real data updates and they should be recorded
to the history system.
b) UPDATEs that are just false data entry, and they should mean
just plain correction UPDATES, with no recording.
This distinguishability is very important, otherwise someone would
define a way to store historic data of changes to the historic data
themselves, and so on.
1) Is the history data frequently needed? Are there a lot of apps
hitting these historic data? Do we need the freedom to query current live
data as well as past data in a uniform manner?
Then i just use one table, with endtimestamp is null meaning
this is a current (alive) record.
Then i write triggers to enforce interval wise integrity to the table,
(e.g. No records A,B exist with A<>B,A,B for the same person, so that
(A.starttimestamp,coalesce(A.endtimestamp,now())) overlaps with
(B.starttimestamp,coalesce(B.endtimestamp,now()))
This way i give people the ability to do what they want with the table.
The triggers do the job of enforcing integrity.
In this case what we mean as historic is
"what users define and input as historic".
Users are in charge here, not the DB.
2) If on the other hand, historic data are just a convinient way
of accessing history data, instead of going to find the backup of this
past day
in the computer room,
then I keep one and
[SQL] Merry Xmas and a Happy New Year
to All! -- -Achilleus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How to Force Transactions to Process Serially on A Table
O Lane Van Ingen έγραψε στις Dec 19, 2005 : > Thanks, that helped. > > Please answer 2 other related questions, if you would: > (1) What must I do to 'Be prepared for serialization failures' (how to > detect, how to handle)? > Do you have a sample? Look at http://www.postgresql.org/docs/7.4/interactive/transaction-iso.html > (2) Also, I am assuming that the effect of all of this is to just force > transactions to wait in line > to be processed serially, and that it only lasts as long as the pl/pgsql > transaction block or > the next COMMIT. > Then transaction isolation SERIALIZABLE is not for this task. What you would do is use the SELECT ... FOR UPDATE construct. Normally you would not care about locking the whole table but only row of interest. > -Original Message- > From: Achilleus Mantzios [mailto:[EMAIL PROTECTED] > Sent: Monday, December 19, 2005 9:25 AM > To: Lane Van Ingen > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] How to Force Transactions to Process Serially on A Table > > O Lane Van Ingen έγραψε στις Dec 19, 2005 : > > > I am using PL/SQL functions on Windows 2003, version 8.0.1. > > > > I have not used explicit PostgreSQL LOCKing before, but I need some advice > > on how properly to use some explicit locking. I think that duplicate key > > violations I am now getting are the result. > > > > I want to force transactions being used to update a table to be processed > on > > a first-come, first-served basis. I want my Pl/sql function to execute to > > completion on each transaction before another starts. > > > > Need some advice on how to do this. From what I can read in the docs, it > > looks like I need to solve the problem by using the following, but doing > so > > gives me an SPI_execution error: > > BEGIN; > > LOCK IN SHARE ROW EXCLUSIVE MODE; > > lock adns_report_hour_history in share row exclusive mode; > > INSERT INTO VALUES ... - or - UPDATE SET > > COMMIT; > > Will this make the next transaction wait until the previous transaction > has > > completed? Do I need to set any config parameters? > > > > If you can include an actual code snippet in the response, it would help > ... > > what you want is to set the xaction isolation level. > > BEGIN; > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; > .. > COMMIT; > > Be prepared for serialization failures though. > > > > > > > > > ---(end of broadcast)--- > > TIP 2: Don't 'kill -9' the postmaster > > > > -- > -Achilleus > > > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match > -- -Achilleus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to Force Transactions to Process Serially on A Table
O Lane Van Ingen έγραψε στις Dec 19, 2005 : > I am using PL/SQL functions on Windows 2003, version 8.0.1. > > I have not used explicit PostgreSQL LOCKing before, but I need some advice > on how properly to use some explicit locking. I think that duplicate key > violations I am now getting are the result. > > I want to force transactions being used to update a table to be processed on > a first-come, first-served basis. I want my Pl/sql function to execute to > completion on each transaction before another starts. > > Need some advice on how to do this. From what I can read in the docs, it > looks like I need to solve the problem by using the following, but doing so > gives me an SPI_execution error: > BEGIN; > LOCK IN SHARE ROW EXCLUSIVE MODE; > lock adns_report_hour_history in share row exclusive mode; > INSERT INTO VALUES ... - or - UPDATE SET > COMMIT; > Will this make the next transaction wait until the previous transaction has > completed? Do I need to set any config parameters? > > If you can include an actual code snippet in the response, it would help ... what you want is to set the xaction isolation level. BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; .. COMMIT; Be prepared for serialization failures though. > > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > -- -Achilleus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] exporting Excel tables into PostgreSQL database with Python
O Christian Kratzer έγραψε στις Dec 13, 2005 : > Hi, > > On Tue, 13 Dec 2005, Jόrgen Kemeter wrote: > > Hi! > > > > Here is a hopefully convenient description of my situation: > > - I have a main folder, containing several subfolders. > > - Every (sub)folder contains one or more .xls - Workbooks. > > - Every Workbook contains one or more different Spreadsheets. > > - The workbooks contain some cells which have Hyperlink addresses to > > other, > > relating workbooks. > > - Some cells in wokrbooks contain comments, which must also be exported. > [snipp] > > I am not familiar with python but we have done imports and exports from > and to excel in perl using the Spreadsheet::ParseExcel and > Spreadsheet::WriteExcel modules from CPAN. Same here but with java, using jakarta-poi. > > Greetings > Christian > > -- -Achilleus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Sybase Connection_Property('number') equivalent in PostGre
O Emil Rachovsky έγραψε στις Dec 2, 2005 : > > I am trying to find out the PostGre equivalent to > the Sybase function Connection_Property (which returns > the connection id, given the parameter 'number') ,but > without success so far. Can anyone tell me how to > retrieve the connection id in PostGre? > Thanks in advance, > Emil I think pg_backend_pid() and pg_stat_get_backend_pid(integeger) are your best bets. > > > > __ > Start your day with Yahoo! - Make it your home page! > http://www.yahoo.com/r/hs > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match > -- -Achilleus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL]
O Oliver Elphick έγραψε στις Nov 24, 2005 : > On Wed, 2005-11-23 at 23:23 -0500, Ken Winter wrote: > > In PL/pgSQL, is there a way to put a *variable* column-name in a dot > > notation reference to a RECORD column? You can do it with a C function. (See dbmirror's pending.c for a reference) > -- -Achilleus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Triggers
O Neil Saunders έγραψε στις Nov 22, 2005 : > And change AFER INSERT to BEFORE INSERT 1) it doesnt make any difference since we are updating a different table than the trigger's one 2) Your email text comes really garbled > -- -Achilleus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Triggers
O Leif B. Kristensen έγραψε στις Nov 22, 2005 : > On Tuesday 22 November 2005 17:25, Achilleus Mantzios wrote: > >O Leif B. Kristensen έγραψε στις Nov 22, 2005 : > >> I'm trying to understand triggers. I have read the documentation in > >> the manual as well as the few pages in the Douglas book about the > >> subject, but I don't see how to implement a trigger that simply > >> updates a 'last_edit' date field in my 'persons' table whenever I do > >> an insert or update into my 'participants' table; that is a trigger > >> that basically does an "UPDATE persons SET 'last_edit' = NOW() WHERE > >> persons.person_id = participants.person_fk". Is that even possible? > > > >smth like: > >foodb=# CREATE or REPLACE FUNCTION upd_dad() RETURNS "trigger" AS ' > >foodb'# BEGIN > >foodb'# UPDATE dad set lastedit=now() where id=new.dadid; > >foodb'# RETURN new; > >foodb'# END; > >foodb'# ' > >foodb-# LANGUAGE plpgsql; > >CREATE FUNCTION > >foodb=# CREATE TRIGGER upd_dad_tg AFTER INSERT OR UPDATE ON kid FOR > > EACH ROW EXECUTE PROCEDURE upd_dad(); > > leif=> create or replace function update_last_edit() returns trigger as > $$ > leif$> begin > leif$> update persons set last_edit=now() where person_id=new.person_fk; > leif$> return new; > leif$> end; > leif$> $$ language plpgsql; > CREATE FUNCTION > leif=> create trigger update_last_edit after insert or update on > participants > leif-> for each row execute procedure update_last_edit(); > CREATE TRIGGER > leif=> insert into participants (participant_id,person_fk) values (1,1); > ERROR: record "new" has no field "last_edit" > CONTEXT: PL/pgSQL function "process_last_edited" line 2 at assignment ^ > leif=> > > What am I missing? > apparently some forgotten process_last_edited() function. -- -Achilleus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Foreign key to 2 tables problem
O Joost Kraaijeveld έγραψε στις Nov 22, 2005 : > Hi, > > Is there a way to create a foreign key to 2 tables: e.g. a bankaccount > table that has a column "owner", that must point to a record in either > the customer or the supplier table? While there are techniques to accomplish this, i see a problem: How do you know if a certain value in owner is to be joined with e.g. customer and not supplier?? The right way is to have 2 columns that can be null, pointing to customer,supplier respectively, and then write a trigger to ensure that exactly one is not null. > > > TIA > > -- -Achilleus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Triggers
O Leif B. Kristensen έγραψε στις Nov 22, 2005 : > I'm trying to understand triggers. I have read the documentation in the > manual as well as the few pages in the Douglas book about the subject, > but I don't see how to implement a trigger that simply updates a > 'last_edit' date field in my 'persons' table whenever I do an insert or > update into my 'participants' table; that is a trigger that basically > does an "UPDATE persons SET 'last_edit' = NOW() WHERE persons.person_id > = participants.person_fk". Is that even possible? > smth like: foodb=# CREATE or REPLACE FUNCTION upd_dad() RETURNS "trigger" AS ' foodb'# BEGIN foodb'# UPDATE dad set lastedit=now() where id=new.dadid; foodb'# RETURN new; foodb'# END; foodb'# ' foodb-# LANGUAGE plpgsql; CREATE FUNCTION foodb=# CREATE TRIGGER upd_dad_tg AFTER INSERT OR UPDATE ON kid FOR EACH ROW EXECUTE PROCEDURE upd_dad(); -- -Achilleus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] idea for a geographically distributed database: how best
O codeWarrior έγραψε στις Nov 17, 2005 : > Sounds like a mis-guided approach to me... > > You might want to consider using latitude and longitude or zip codes or > taking more of a traditional "GIS" approach rather than duplicating data > across redundant databases. > > Another issue is that you end up having to query every database to find > proximity... Suppose you have 500 "cells" ? You now have to invoke some sort > of RPC mechanism on 499 other RDBMS, manage 500 user names and passwords, > retrieve potentially 500 recordsets, merge the data, etc... > > Your problems will continue to get more and more complex... You are better > off with a single RDBMS and a single schema... Andy, i agree with what codeWarrior says. But if you are interested in replication, dbmirror is very elegant (altho not as trendy) simple, and highly customizable replication solution. I have heavily modified dbmirror to even support Asynchronous Conditional row grained Foreign key dependency Traversal oriented Lazy Replication!!! (which ofcourse nobody wants :) > > > > > "Andy Ballingall" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > > Hello, > > > > I've got a database for a website which is a variant of the 'show stuff > > near > > to me' sort of thing. > > > > Rather than host this database on a single server, I have a scheme in mind > > to break the database up geographically so that each one can run > > comfortably > > on a small server, but I'm not sure about the best way of implementing it. > > > > Here's the scheme: > > > > > > Imagine that the country is split into an array of square cells. > > Each cell contains a database that stores information about people who > > live > > in the area covered by the cell. > > > > There's one problem with this scheme. What happens if you live near the > > edge > > of a cell? > > > > My solution is that any inserted data which lies near to the edge of cell > > A > > is *also* inserted in the database of the relevant neighbouring cell - > > let's > > say cell B. > > > > Thus, if someone lives in cell B, but close to the border with cell A, > > they'll see the data that is geographically close to > > them, even if it lies in cell A. > > > > > > > > Is this a common pattern? > > > > I could, of course, simply find every insert, update and delete in the > > application and alter the code to explicitly update all the relevant > > databases, but is there a more elegant way of simply saying: "Do this > > transaction on both Database A and Database B" monotonically? > > > > I've had a look at some replication solutions, but they all seem to > > involve > > replicating an entire database. The advantage of my scheme is that if I > > can > > distribute my application over large numbers of small servers, I'll end up > > with more bangs for the buck, and it'll be much easier to manage growth by > > managing the number of servers, and number of cells hosted on each server. > > > > Thanks for any suggestions! > > Andy Ballingall > > > > > > ---(end of broadcast)--- > > TIP 3: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > > > > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match > -- -Achilleus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Arrya variable as argument to IN expression
O Emil Kaffeshop έγραψε στις Nov 17, 2005 :
> Hello I am trying the following:
> a INTEGER[] := ''{10,15,20}'';
> b INTEGER := 15;
> flag BOOLEAN := FALSE;
>
> SELECT b IN (a) INTO flag; !!! does not work
contrib/intarray and
SELECT intset(b) ~ a::int[] INTO flag;
are your friends.
>
> Is it legal to expect the array to be interpret as
> list of integers which IN expression takes ?
>
> Best Regards
> Emil kafegiiski
>
>
>
>
> __
> Yahoo! FareChase: Search multiple travel sites in one click.
> http://farechase.yahoo.com
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>
--
-Achilleus
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Migrating database from postgresql 7.4.6 to postgresql
O A. Kretschmer έγραψε στις Nov 9, 2005 : > am 08.11.2005, um 23:18:35 -0800 mailte Louise Catherine folgendes: > > Hi, > > I would like to migrating my database from postgresql > > 7.4.6 to postgresql 8.0.3. > > Why not 8.1.0? > > > > Are there any problem during migrating database? a > > casting problem or sintax problem, or other problem? > > I dont know about problems. Install the new database (on the same > machine, use a different port), and use the pg_dump from the newer > version to dump the old db in the new db. Or better consult HISTORY. It is in the root of the tarball. > > > HTH, Andreas > -- -Achilleus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Welcome to the pgsql-sql list!
For some reason i got unsubscribed from sql for no apparent reason. (I cant recall anything close to that, and i never intented to unsubscribe from -sql list) The [EMAIL PROTECTED] person might be interested in investigating this. Thank you. -- -Achilleus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] postgres on the comman line
O "Michael Hφller" έγραψε στις Sep 22, 2005 : > > > Hello, > > I like to automate a daily check and like to run select statement via > cron but this seems to be more tricky than I thought > > I tried the following: > su postgres -c "select count(*) from TABLE where xx;" Firstoff, 'select' is nor a unix command, neither a postgresql program. So you need something like: su postgres -c "psql -c 'select count(*) from TABLE where xx'" If you are the only one with an account on the machine then you might try to tweak ~postgres/data/pg_hba.conf and set method 'trust' for local access (no tcpip,psql without the -h option) . Also you could set env variables PGPASSWORD, PGUSER. > > I have the probelm that I am allways asked for the password - I did not > find a way how to pass the password. > > Also I like to direct the result to a file, I assume I can do this via > > but not haveing passed the first problem I did not check this. > > I am happy for every hint > > Thanks a lot > Michael > > > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > -- -Achilleus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Inheritance (general hints wanted)
Judging from the replies i got, it seems that inheritance is even less used than i initially thought. I think that the OO term is a little too much advertised in pgsql advocacy various acts, than actually engineered. However, OO in pgsql besides being a traditionally cool acronym, it also *could* prove to be extremely useful when dealing with real problems. Just a question, to all those who have implemented inheritance in pgsql: Are there any plans for dealing with the current problems? Is it foundamentally hard to fix these problems? -- -Achilleus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Inheritance (general hints wanted)
O Achilleus Mantzios έγραψε στις Sep 20, 2005 : > > Hi, > > I think i have reached a point in my PgSQL years > that i am seriously thinking of using inheritance. > > The situation is simple: An new entity (tanker vessels crew) > is about to be modeled, and i suspect > there will be a future need to include > the rest of 'workers' besides 'sailors', > IOW i suspect a future need to generalize. > > So i am thinking of a two-level tree hierarchy, > where there is a simple table emp (employee), > and a direct child 'tankerscrew'. Later > there may be 'masons', 'office_employees' > and so on. Each table may be managed by its own applications, whereas the accounting software will only need to read and *update* the generic top level 'employee' table. That would be great but unfortunately it isnt how it works in PostgreSQL currently... If PRIMARY KEYS were to be shared, a great future (real) feature would be a "isinstanceof" -like function on the PRIMARY KEY, which tells if a row of the specific PK is from a certain table in the hierarchy. > > The problem is that while i find this approach > attractive, i have never used PostgreSQL > inheritance exhaustively and in production scale. > > Furthermore, i also feel that not many > people have used inheritance in a massive fashion > (besides playing with a test db). > > Of course i can do it with separate uncoupled tables, > that can be later related with some views. > > So that leads to the final question: > -What are the main PROs of inheritance that cant be beat > by other solutions, and > -Are there any traps someone must have in mind when he > prepares for such a design? > > Thanx in advance. > -- -Achilleus ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Inheritance (general hints wanted)
Hi, I think i have reached a point in my PgSQL years that i am seriously thinking of using inheritance. The situation is simple: An new entity (tanker vessels crew) is about to be modeled, and i suspect there will be a future need to include the rest of 'workers' besides 'sailors', IOW i suspect a future need to generalize. So i am thinking of a two-level tree hierarchy, where there is a simple table emp (employee), and a direct child 'tankerscrew'. Later there may be 'masons', 'office_employees' and so on. The problem is that while i find this approach attractive, i have never used PostgreSQL inheritance exhaustively and in production scale. Furthermore, i also feel that not many people have used inheritance in a massive fashion (besides playing with a test db). Of course i can do it with separate uncoupled tables, that can be later related with some views. So that leads to the final question: -What are the main PROs of inheritance that cant be beat by other solutions, and -Are there any traps someone must have in mind when he prepares for such a design? Thanx in advance. -- -Achilleus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] not sure about constraints
O Jerome Alet έγραψε στις Sep 2, 2005 : > Hello, > > I've got 4 tables : > > CREATE TABLE tableA (id SERIAL PRIMARY KEY NOT NULL, > blahA TEXT); > > CREATE TABLE tableB (id SERIAL PRIMARY KEY NOT NULL, > blahB TEXT); > > CREATE TABLE tableC (id SERIAL PRIMARY KEY NOT NULL, > ida INT4 REFERENCES tableA(id), > idb INT4 REFERENCES tableB(id), > blahC TEXT); > > CREATE TABLE tableD > (id SERIAL PRIMARY KEY NOT NULL, > ida INT4, > idb INT4, > blahC TEXT, > CONSTRAINT checkAB FOREIGN KEY (ida, idb) REFERENCES tableC(ida, idb)); The above statement is in error. In order to create FK to another table (tableC), you must do this on tableC's PK (id) or some UNIQUE key in general. > > Is the definition of tableD sufficient, or should I do it this way > instead : > > CREATE TABLE tableD > (id SERIAL PRIMARY KEY NOT NULL, > ida INT4 REFERENCES tableA(id), > idb INT4 REFERENCES tableB(id), > blahD TEXT, > CONSTRAINT checkAB FOREIGN KEY (ida, idb) REFERENCES tableC(ida, idb)); > Provided you have done something like ALTER TABLE tablec add CONSTRAINT tablec_ukey UNIQUE (ida,idb); Which means that each compination of the pair (ida,idb) in tablec is unique in tablec, i.e. NO 2 rows of tablec have the same (ida,idb), Then you can go which chioce 1), since it is guaranteed that ida belongs to tablea, and idb belongs to tableb by tablec's contraints. So the extra FKs defined in choice 2) are indeed reduntant. But the point here is to understand, that always when we point to another table, we point at some Unique key of that table. > which looks superfluous to me. > > ??? > > Thanks in advance > > Jerome Alet > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > -- -Achilleus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] REINDEX DATABASE
O Achilleus Mantzios έγραψε στις Jul 28, 2005 : > O Chris Browne έγραψε στις Jul 27, 2005 : > > > I'll try to postpone the next reindexdb at the end of august, > and get some numbers then. > However the big difference in performance as i told was near the 2GB > "threshold", and at *that* point (and maybe for different reasons) > performance gain was remarkable. > Well, today i run reindexdb and i noticed a decrease in database size, from 2890148K to 2527552K, about 12.54% decrease in size. However i have not an estimation of the distribution of the SQL commands (INSERT,UPDATE,DELETE) over August. I am running 7.4.6. > > -- -Achilleus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] REINDEX DATABASE
O Chris Browne έγραψε στις Jul 27, 2005 : > [EMAIL PROTECTED] (Achilleus Mantzios) writes: > > O Chris Browne έγραψε στις Jul 26, 2005 : > >> You can get a savings of about 4% of the space, but at the cost of > >> taking an appreciable outage during which the database is not usable. > > > > 1st not all database is unusable, during the whole reindexdb run, > > 2nd outage outside office hours is acceptable for those who apply. > > I'm not willing to assume that. > > I help support some applications where "outages outside office hours" > are acceptable; I help support some other applications for which > "office hours" are 24 hours per day, 7 days per week, and such an > outage would be deemed decidedly unacceptable. > > >> I wouldn't expect the 4% savings in space to lead to a particularly > >> measurable improvement in performance, certainly not one worth the > >> outage. > > > I am just saying that the common saying "reindex is not needed for > > 7.4+" maybe is not true in all circumstances. > > Let me quote the release notes for 7.4.1: > > "Make free space map efficiently reuse empty index pages, and other > free space management improvements > > In previous releases, B-tree index pages that were left empty > because of deleted rows could only be reused by rows with index > values similar to the rows originally indexed on that page. In > 7.4, VACUUM records empty index pages and allows them to be reused > for any future index rows." > > <http://www.postgresql.at/pg/pgsql/doc/html/release-7-4.html> > > In versions earlier than 7.4, running a REINDEX periodically was > *essential* if you had update patterns consistent with the (remarkably > common) scenario described above. > > This reason to reindex (which was the main reason we required > reindexing when using 7.2) has been resolved and gone away in 7.4. > > There may be other factors that could mandate REINDEX; as far as I can > tell, the main such factor that remains would be where a table sees > enormous numbers of updates but is not VACUUMed often enough. I'll try to postpone the next reindexdb at the end of august, and get some numbers then. However the big difference in performance as i told was near the 2GB "threshold", and at *that* point (and maybe for different reasons) performance gain was remarkable. > > _That_ scenario isn't consistent with what you describe, as it would > be expected to involve a whole lot more than 4% growth in the size of > the database. > -- -Achilleus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] REINDEX DATABASE
O Chris Browne έγραψε στις Jul 26, 2005 : > [EMAIL PROTECTED] (Achilleus Mantzios) writes: > > O Christopher Browne έγραψε στις Jul 26, 2005 : > > > >> > Hello > >> > > >> > Would you like to advice to use REINDEX DATABASE on regular basis ? > >> > > >> > if (yes) > >> > how it should be connected with VACUUM FULL ANALYZE which is run > >> > regularly ? (reindex before vacuum or vacuum before reindex?) > >> > > >> > else > >> > haw to determine _when_ to run REINDEX ? > >> > >> If you are doing ordinary VACUUM ANALYZE frequently enough, it > >> shouldn't be necessary to either VACUUM FULL or REINDEX. > >> > >> Back in the 7.2 days, there were sorts of update patterns that would > >> mandate reindexing every so often, as you could get cases where index > >> pages would be very sparsely populated. That was alleviated in > >> version 7.3, I believe, and was clearly evident in 7.4. > >> > >> You know you need to REINDEX if analysis of an index shows that it is > >> sparsely populated. This generally shows up if you do an analyze on > >> the table and find an index has more pages than tuples. > >> > >> But if you run VACUUM reasonably frequently, this shouldn't be > >> necessary... > > > > I vacuum analyze every hour, however after reindexdb > > my (currently) 2.47 GB db is reduced to 2.37 GB, > > thus helping both db-wise and freebsd_cache-wise boost performance. > > > > All the above in 7.4.6. > > > > I am not arguing that i have done detailed analysis of the > > situation, i'm just saying that i have witnessed a performance gain after > > running contrib/reindexdb every month (or so). > > That doesn't strike me as being a material improvement, and it comes > at a pretty high cost. The numbers above are just the numbers reported after doing a live reindex on the spot after deciding to answer to this email. In the past (when the db was smaller) and for the same version, i have come across much "striking" numbers, and much more striking boost gain. (e.g. a 2+ GB db directory turning to a ~1.5 GB dir). BTW whats the limit of kern.ipc.shmmax for 32-bit intel? ~ 2^31 = ~ 2 GB. In those cases i noticed severe boost gain. (And of course the 1 hour vacuum analyze schedule was always there) > > You can get a savings of about 4% of the space, but at the cost of > taking an appreciable outage during which the database is not usable. 1st not all database is unusable, during the whole reindexdb run, 2nd outage outside office hours is acceptable for those who apply. > > I wouldn't expect the 4% savings in space to lead to a particularly > measurable improvement in performance, certainly not one worth the > outage. > I am just saying that the common saying "reindex is not needed for 7.4+" maybe is not true in all circumstances. -- -Achilleus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] REINDEX DATABASE
O Christopher Browne έγραψε στις Jul 26, 2005 : > > Hello > > > > Would you like to advice to use REINDEX DATABASE on regular basis ? > > > > if (yes) > > how it should be connected with VACUUM FULL ANALYZE which is run > > regularly ? (reindex before vacuum or vacuum before reindex?) > > > > else > > haw to determine _when_ to run REINDEX ? > > If you are doing ordinary VACUUM ANALYZE frequently enough, it > shouldn't be necessary to either VACUUM FULL or REINDEX. > > Back in the 7.2 days, there were sorts of update patterns that would > mandate reindexing every so often, as you could get cases where index > pages would be very sparsely populated. That was alleviated in > version 7.3, I believe, and was clearly evident in 7.4. > > You know you need to REINDEX if analysis of an index shows that it is > sparsely populated. This generally shows up if you do an analyze on > the table and find an index has more pages than tuples. > > But if you run VACUUM reasonably frequently, this shouldn't be > necessary... I vacuum analyze every hour, however after reindexdb my (currently) 2.47 GB db is reduced to 2.37 GB, thus helping both db-wise and freebsd_cache-wise boost performance. All the above in 7.4.6. I am not arguing that i have done detailed analysis of the situation, i'm just saying that i have witnessed a performance gain after running contrib/reindexdb every month (or so). > -- -Achilleus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Are long term never commited SELECT statements are a
O Erik Wasser έγραψε στις Jul 21, 2005 :
> Hello List,
>
> I've written an application in perl using DBI with MySQL (no transaction
> support). Then we decide to migrate it to postgresql
> (postgresql-8.0.1-r4).
>
> At first we were using 'AutoCommit => 1' with the application. That
> means that every statement will be commited right away.
>
> Then I discovered the 'magic of transactions' and set AutoCommit to 0.
> Then I rewrite many UPDATE and INSERT statements with support for
> commit and rollback. BUT: the SELECT statements were untouched (and
> that was mistake I think).
>
> Now I've got here a blocking problem. Severel SQL statements (like
> renaming a field or UPDATE of a field) are blocked until I kill a
> certain task. This task DOES only the INSERTS and UPDATES with a
> transaction and the SELECT statements are not within an transaction.
> And this task is a long term running task (some kind of daemon) so the
> SELECT transactions will never be commited. Are long term never
> commited SELECT statements are a problem and could that lead to
> blocking other queries?
>
> To put it in annother way: what kind of thing I produced with the
> following pseudocode?
>
> # open database
> $DBH = DBI->connect(...,USERNAME,PASSWORD, { RaiseError => 1, AutoCommit
> => 0 });
>
> while (true)
> {
># do some select
>SELECT ... FROM ...
># do some more
>SELECT ... FROM ...
>
>if (condition)
> # do an UPDATE/INSERT
> eval {
> UPDATE/INSERT/...
> $DBH->commit;
> };
> if ($@) {
> warn "Transaction aborted: $@";
> eval { $DBH->rollback };
> }
>}
> }
>
> Is this some kind of nested transaction? Can there be a problem with
> this code?
You mean savepoints?
In 8.x there is the feature of nested xactions.
But apparrently in your script you dont use them.
In general when working with BEGIN/COMMIT/ROLLBACK blocks
always be sure that you either rollback or commit
your transaction.
The need for this is more visible when using connection pools.
I am not familiar with the DBI semantics, but
in your case it would be quite possible for
some job to block if another job's xaction has
already managed to update a row which the 1st job's xaction
tries to update too.
In any case, pure selects dont need to be in a xaction
unless you want to lock these rows, in which case
you use "FOR UPDATE".
In general you must dig a little deeper into PostgreSQL's
xaction mechanisms and policies,
since migrating from mysql requires some effort
regarding all new (to you) postgresql features.
The documentation (in the usual url) is superb.
>
> Thanks for your help!
>
>
--
-Achilleus
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Alias to a type
O Sean Davis έγραψε στις Jun 22, 2005 : > > On Jun 22, 2005, at 7:42 AM, Achilleus Mantzios wrote: > > > O Veikko MΞ΄kinen ΞΞ³ΟΞ±ΟΞ΅ ΟΟΞΉΟ Jun 22, 2005 : > > > >> Hey, > >> > >> Is it possible to create a new type as an alias to a pre-defined > >> type? I > >> use "USERID varchar(20)" in almost every table I have I'd like to make > >> an alias for that type eg. > >> > >> > >>create type myschema.useridtype as varchar(20); > >> > >> > > > > Try something like > > > > CREATE DOMAIN my_integer AS INTEGER; > > Just for my own edification, does creating a "simple" domain like this > then require a whole set of functions for indexing, etc., like other > more complex user-defined types, or will postgres "do the right thing"? In the above example you may safely consider indexing columns of type my_integer as indexing INTEGERs. > > Thanks, > Sean > > -- -Achilleus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Alias to a type
O Veikko Mδkinen έγραψε στις Jun 22, 2005 : > Hey, > > Is it possible to create a new type as an alias to a pre-defined type? I > use "USERID varchar(20)" in almost every table I have I'd like to make > an alias for that type eg. > > >create type myschema.useridtype as varchar(20); > > Try something like CREATE DOMAIN my_integer AS INTEGER; > I might have to alter the type some day and this way I'd have to just > re-define the alias. Can this be achieved by creating a new type with > CREATE TYPE? What are the input/output functions in the CREATE TYPE > definition? > > Thanks. > > > -veikko > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > -- -Achilleus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] to listadmin
O Kenneth Gonsalves έγραψε στις Jun 7, 2005 : > On Tuesday 07 Jun 2005 1:35 pm, Richard Huxton wrote: > > I'm not sure it is from the list - it appears to be something setup > > by [EMAIL PROTECTED] > > apparently he is a member of the list - so mail from any 'untrusted' > guy like me gets this message. (anyone else getting it?) Yes i receive one of the same. > > -- -Achilleus ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] What is faster?
O ON.KG έγραψε στις Jun 7, 2005 : > RH> ON.KG wrote: > >> Hi All! > >> > What is faster - SLECTion data from one large table (200 000 - 300 000 > records), or SELECTion from a few small tables (example, 2 tables 150 > 000 records each)? > >> > >> For example i have two large tables > >> Structure of tables is same - has two fields - id, ip > >> > >> Now i'm using two selection from each in one transaction > >> Each of them selects only one record > >> selection clase like WHERE ip = 'xxx.xxx.xxx.xxx' > >> so it is searches existance of IP in each table > >> > >> tables are clustered > > RH> OK - so the tables aren't updated frequently, I assume. Do you have an > RH> index on "ip"? > > Yes > > >> about cached in RAM - i'm novice in Postgresql - how does it work? > > RH> The operating-system will keep frequently used disk-blocks in memory. > RH> You don't have to do anything. Have you done any performance tuning in > RH> your postgresql.conf file? If not, try reading: > RH>http://www.powerpostgresql.com/PerfList > > Server Administrator says, he did > > >> now i need to make much faster as it is possible > >> and have an idea just merge two tables in one - will it help me? > > RH> If they hold the same information, they probably shouldn't have been > RH> split in the first place. > > Content of tables is not absolutely same > One has one kind of IPs, second - others > > And there's one more addition in question - if I will merge tables, > in new table will be set new additional field - `type` char - to > determine type of IP > > Thank You Just a thought, also you could try if converting your ip from varchar(32) to int8 (unsigned int4) would make any difference, since the index on int8 would be more efficient. > > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > -- -Achilleus ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] 'true'::TEXT::BOOLEAN
O Michael Glaesemann έγραψε στις Jun 3, 2005 : > > On Jun 3, 2005, at 9:23 PM, Markus Bertheau β? wrote: > > > This also bypasses the built in postgresql boolean literal parsing. > > > > I think casting from text to boolean should be possible, and use the > > same algorithm that's used when casting from "unknown" to boolean. > > Actually, looking at the system tables, I don't think it is. There > don't appear to be any casts to (or from) boolean. I may be looking > at it wrong, but that's how it appears to me. Corrections, anyone? > > select type_source.typname as source, type_target.typname as target > from pg_cast > join pg_type type_source on (castsource = type_source.oid) > join pg_type type_target on (casttarget = type_target.oid) > where type_target.typname = 'bool' > or type_source.typname = 'bool' > order by type_source.typname; > > source | target > + > (0 rows) > > This is in v8.0.3 Also according to the docs: http://www.postgresql.org/docs/current/static/datatype-boolean.html "Tip: Values of the boolean type cannot be cast directly to other types (e.g., CAST (boolval AS integer) does not work). This can be accomplished using the CASE expression: CASE WHEN boolval THEN 'value if true' ELSE 'value if false' END." I suppose the reverse must be true also. > > Michael Glaesemann > grzm myrealbox com > > PS. Please don't top post. > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- -Achilleus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] 'true'::TEXT::BOOLEAN
O Markus Bertheau β^Ψ έγραψε στις Jun 3, 2005 :
> Π? Π?Ρ?Π½, 03/06/2005 Π² 14:20 +0300, Achilleus Mantzios ΠΏΠΈΡ?Π΅Ρ?:
> > O Markus Bertheau Ξ²^Ψ ΞΞ³Ο?Ξ±Ο?Ξ΅ Ο?Ο?ΞΉΟ? Jun 3, 2005 :
> >
> > > Hi,
> > >
> > > What's the type I need to convert text to before I can convert it to
> > > boolean?
> >
> > just 't' will suffice.
>
> Well, that's not my question. I have a plpgsql function like that:
>
> CREATE FUNCTION object_new(class TEXT, properties TEXT[])
>
> which I call like
>
> object_new('Car', ARRAY['color', 'red', 'new', 'true'])
>
> That means set color to red and new to true. In the function I need to
> call
>
> object_set_boolean_property(object_id INT, property_name TEXT,
> property_value BOOLEAN)
>
> And I can't call it with a TEXT variable, because casting from TEXT to
> BOOLEAN isn't possible.
Then use the
case when ... then ... when ... then ... else ... end
construct, e.g.
case when mytext='true' then 't'::boolean else 'f'::boolean end
Hmm, why dont you leave it as 'true' or 'false' without any castings.
>
> Markus
>
--
-Achilleus
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] 'true'::TEXT::BOOLEAN
O Markus Bertheau β^Ψ έγραψε στις Jun 3, 2005 : > Hi, > > What's the type I need to convert text to before I can convert it to > boolean? just 't' will suffice. > > Markus > -- -Achilleus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] View unique rowid
O David Klugmann έγραψε στις Jun 1, 2005 : > > Hi > Is it possible to refer to a unique row identifier on a view ? > > I have the following view but in a subsequent select I need to refer to > each row's unique identifier and I know oid's are not valid for a view. Provided your ids are 4 bytes long, you could try to build an artificial id as > > create view persontransit > as > select personid, planet, name as aspectname, position as planetposition, SELECT personid::int8 as viewid, > position+angle as transitposition > from personplanet, aspect > union > select personid, planet, name as aspectname, position as planetposition, SELECT X'1'::int8<<32 | personid::int8 as viewid... > position-angle as transitposition > from personplanet, aspect > where name != 'OPPOSITION'; > > Many thanks > > David > that way the viewid is unique, + you know which part of the view it represents by masking on the 33th bit (4294967296), while you can get the actual personid by masking with X'' (4294967295) > > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > -- -Achilleus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
O Joe Conway έγραψε στις May 23, 2005 :
> Markus Bertheau wrote:
> > why does SELECT ARRAY(SELECT 1 WHERE FALSE) return NULL instead of
> > ARRAY[] resp. '{}'?
> >
>
> Why would you expect an empty array instead of a NULL? NULL is what
> you'd get for other data types -- for example:
One could ask in the same fashion why someone would want a table
if this table contains no rows.
A null value may mean "dont know",
wheras a '{}' (empty) value may mean "empty set".
For instance lets consider the case where an array holds
the factors of a polynomial formula.
An null value might mean that the person defining
the formulas haven't been bothered with this one yet.
An empty value might mean that the person indicates
that has worked on this particular one, but he/she has no data yet.
Ok extreme cases, but to me there is a clean distinction
between a null array and an empty array.
Also what is definately needed is arrays that may contain
null values.
>
> regression=# SELECT (SELECT 1 WHERE FALSE) IS NULL;
> ?column?
> --
> t
> (1 row)
>
> Joe
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
>
--
-Achilleus
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Need clarification
O [EMAIL PROTECTED] έγραψε στις May 23, 2005 : > > > > > how can retrieve rows where the datedifference is more than 30... more than 30 in what units? years,days,milliseconds?? > assume there exists a field named 'datacreated' which is of type 'date'. I > need to compare it with the current date and need to extract the matched > rows > select * from footable where datacreated = now()::date; > thanks in advance. > > Palanivel > > Important Email Information :- The information in this email is > confidential and may be legally privileged. It is intended solely for > the addressee. Access to this email by anyone else is unauthorized. If > you are not the intended recipient, any disclosure, copying, distribution or > any action taken or omitted to be taken in reliance on it, is prohibited > and may be unlawful. If you are not the intended addressee please contact > the sender and dispose of this e-mail immediately. > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > -- -Achilleus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] people who buy A, also buy C, D, E
O Christoph Haller έγραψε στις Apr 26, 2005 : > Dan Langille wrote: > > > > The goal of my query is: given a book, what did other people who > > bought this book also buy? I plan the list the 5 most popular such > > books. In reality, this isn't about books, but that makes it easier > > to understand I think. > > > > We have a table of customer_id (watch_list_id) and book_id > > (element_id). > > > > freshports.org=# \d watch_list_element > > Table "public.watch_list_element" > > Column | Type | Modifiers > > ---+-+--- > > watch_list_id | integer | not null > > element_id| integer | not null > > Indexes: > > "watch_list_element_pkey" primary key, btree (watch_list_id, > > element_id) > > "watch_list_element_element_id" btree (element_id) > > Foreign-key constraints: > > "$2" FOREIGN KEY (watch_list_id) REFERENCES watch_list(id) ON > > UPDATE CASCADE ON DELETE CASCADE > > "$1" FOREIGN KEY (element_id) REFERENCES element(id) ON UPDATE > > CASCADE ON DELETE CASCADE > > > > freshports.org=# > > > > I have a query which returns the needed results: > > > > SELECT W.element_id > >FROM watch_list_element W > > WHERE w.watch_list_id in (select watch_list_id from > > watch_list_element where element_id = 54968) > >GROUP BY W.element_id > >ORDER BY count(W.watch_list_id) DESC > > LIMIT 5; > > > > But performance is an issue here. So I'm planning to calculate all > > the possible values and cache them. That is, given each element_id in > > a watch_list, what are the top 5 element_id values on all the lists > > on which the original element_id appears? > > > > I'm having trouble constructing the query. I'm not even sure I can > > do this in one select, but that would be nice. Examples and clues > > are appreciated. > > > > Any ideas? > > > > Thank you. > > -- > > Just two ideas. > > 1) Older Postgres versions are notorious for being slow > on "IN" clauses. > Does this one (untested) perform better: > > SELECT W.element_id, count(W.watch_list_id) > FROM watch_list_element W > WHERE EXISTS > (SELECT * FROM watch_list_element E > WHERE E.element_id = 54968 AND W.watch_list_id = E.watch_list_id) > GROUP BY W.element_id > ORDER BY 2 DESC > LIMIT 5; > > 2) I suspect calculating all possible values would require time and > an enormous cache buffer in size as well as re-calculating pretty often. > So my approach would be trying to tune the query before introducing > cached results. AFAIK, problems like this fall into the "Data Mining" field, and often their solution go beyond some DB arrangments. A little research wouldn't hurt, IMO. > > HTH > > Regards, Christoph > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > -- -Achilleus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How to add 1 hour in a date or time stamp?
O Dinesh Pandey έγραψε στις Apr 18, 2005 : > > > How to add 1 hour in a date or time stamp? # SELECT now() + '1 hour'::interval; > > Regards > Dinesh Pandey > > > > -- > > > > > > -- -Achilleus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] order by question
O Gary Stainburn έγραψε στις Mar 9, 2005 : > Hi folks. > > I seem to remember somewhere being shown how to bump specific rows to > the top of a list; something along the lines of: > > select c_id as key, c_des as value from customers order by c_id = 7, > c_id = 160, value; use the case ... when .. then ... when ... then ... else ... construct. > > however, although the statement is accepted the two rows specified are > not bumped to the top of the list, but instead appear in their correct > position in the order by value part. > > Is it possible and if so how do I do it? > -- -Achilleus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Timestamp with timezone question.
O Bruno Wolff III έγραψε στις Feb 22, 2005 : > On Mon, Feb 21, 2005 at 16:16:04 +0200, > Achilleus Mantzios <[EMAIL PROTECTED]> wrote: > > > > Since pgsql always converts a timestamptz to UTC, we have lost > > the information of the Sender's local timezone. > > > > Should i go with a separete date and timetz ? > > Someone else gave you a recommended solution. > > However there was a discussion on the order of 6 months ago about changing > the timestamptz type to keep this information. Nothing will be changing > in the near future, but you may (or may not) find the discussion useful. Thanx, i'll check it out sometime. Apart from that, having a new (version of a) type (timestamptz) just to provide a convinience function isnt too useful. > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > -- -Achilleus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Timestamp with timezone question.
O Andrew - Supernews έγραψε στις Feb 21, 2005 : > On 2005-02-21, Achilleus Mantzios <[EMAIL PROTECTED]> wrote: > > Consider a schema designed to store internet mail. > > > > Since pgsql always converts a timestamptz to UTC, we have lost > > the information of the Sender's local timezone. > > > > Should i go with a separete date and timetz ? > > No. Consider instead storing a timestamptz with the actual time of the > mail, and a separate field with an interval representing the zone offset. > Then you can use AT TIME ZONE to recover the sender's local time. > > e.g. (this table has columns serial, timestamptz, interval) > > insert into dtz values (DEFAULT, > '2005-03-21 07:05:00 -0800', > '2005-03-21 07:05:00 -0800'::timestamp > - '2005-03-21 07:05:00 -0800'::timestamptz at time zone 'UTC' > ); > > (the timestamp - timestamptz thing is just a reasonably reliable way of > getting the timezone offset without complicated parsing.) > > select * from dtz; > id | t| z > ++--- > 1 | 2005-03-21 15:05:00+00 | -08:00:00 > (1 row) > > select *, t at time zone z as ot from dtz; > id | t| z | ot > ++---+- > 1 | 2005-03-21 15:05:00+00 | -08:00:00 | 2005-03-21 07:05:00 > (1 row) Cool thanx. I ended up displaying the actual date header field of the SMTP message (just like the yahoo guys do). > > -- -Achilleus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Timestamp with timezone question.
AFAIK, the input for a timestamptz is converted and stored as UTC. And outputing a timezonetz value converts the internally stored UTC value to the current locale's timezone. So there is not a way to actually store the original TZ itself, whereas the timetz type clearly does that. Consider a schema designed to store internet mail. Since pgsql always converts a timestamptz to UTC, we have lost the information of the Sender's local timezone. Should i go with a separete date and timetz ? -- -Achilleus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] parsing a string with a hexadecimal notation
O KΦPFERL Robert έγραψε στις Feb 9, 2005 :
> I intend to retrieve an int value in an integer variable from a string with
> a hexadecimal notation of a number.
> Which function is appropriate to do
> i int4
> i = ???('BEAF')
You can do something like
foodb=# SELECT int4(X'FF'::bit varying);
int4
--
255
(1 row)
but i suspect you must prepare your statement out of sql.
E.g. in java:
String hex="FF";
st = con.prepareStatement("select int4(X'"+hex+"'::bit varying");
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
--
-Achilleus
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] BLOBs vs BYTEA
O Dennis Sacks έγραψε στις Jan 31, 2005 : > Sam Adams wrote: > > >Anyway, I was wondering which would be a better way to store a large > >amount of files each a few megabytes in size. There could be hundreds of > >thousands of files altogether. If stored as BYTEAs this would put them > >all in a single table. Would this effect performance considerablely? I > >assume if there were thousands then it would. But if the data is stored > >as BLOBs then aren't they store inside the database just in another > >table? Wouldn't this also be undesirable. Would it be better to store > >them normally on the file system and just provide the path and file name > >in the database. Obviously this wont provide any security or backup but > >would it make sense to do it this way with such a large amount of data? > > > > > This is one of those age old debates. I've done both, and I'll tell you > that in my experience it is less of a headache to store the files in the > filesystem (which is what filesystems are designed for) and store the > metadata in the database. I think if you are doing replication or write in java, you'll be much happier with bytea. > > Dennis Sacks > [EMAIL PROTECTED] > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > -- -Achilleus ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] contrib/intarray doc/tutorial needed
O Oleg Bartunov έγραψε στις Jan 26, 2005 : > Achilleus, > > I'm looking for better documentation for contrib/intarray. > As an experienced user of this module, could you prepare > tutorial on using the module in real life applications ? Oleg, i wrote to you, but i suspect my machine is spam listed on your site. If this is the case, i'll try to contact you from yahoo. > Seems, many people just don't understand that intarray could > help them in many situations. > > Regards, > Oleg > _ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 > -- -Achilleus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] datediff is there something like it?
O Joel Fradkin έγραψε στις Jan 25, 2005 :
> Hi all working my way through our views and all is going very well.
>
> We use datediff in MSSQL a bit and I read about the field1::date -
> field2::date to return the days numerically.
>
> Is there any way to get months and years besides guessing days / 30 for
> months etc?
Go to
http://www.postgresql.org/docs/7.4/interactive/functions-datetime.html
Check out smth like...
SELECT 'Achilleus is ' || date_part('years',age(timestamp '1969-01-31'))
|| ' years and ' || date_part('months',age(timestamp '1969-01-31')) || '
months old';
?column?
-
Achilleus is 35 years and 11 months old
(1 row)
>
>
>
> Joel Fradkin
>
>
>
> Wazagua, Inc.
> 2520 Trailmate Dr
> Sarasota, Florida 34243
> Tel. 941-753-7111 ext 305
>
>
>
> [EMAIL PROTECTED]
> www.wazagua.com
> Powered by Wazagua
> Providing you with the latest Web-based technology & advanced tools.
> C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
> This email message is for the use of the intended recipient(s) and may
> contain confidential and privileged information. Any unauthorized review,
> use, disclosure or distribution is prohibited. If you are not the intended
> recipient, please contact the sender by reply email and delete and destroy
> all copies of the original message, including attachments.
>
>
>
>
>
>
>
>
>
--
-Achilleus
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] pg_user relationship does not exist
O Joel Fradkin έγραψε στις Jan 20, 2005 : > Not sure this is the correct place to ask, but when I log in with pgadminIII > I get that message. Maybe do extensive logging (show queries,etc..)(see in postgresql.conf), and then examine your log to see what pgadminIII is asking for. > > > > Any ideas how to fix? Do I need to redo my data base or something? > > > > Joel Fradkin > > > > Wazagua, LLC > 2520 Trailmate Dr > Sarasota, Florida 34243 > Tel. 941-753-7111 ext 305 > > > > [EMAIL PROTECTED] > www.wazagua.com > Powered by Wazagua > Providing you with the latest Web-based technology & advanced tools. > C 2004. WAZAGUA, LLC. All rights reserved. WAZAGUA, LLC > This email message is for the use of the intended recipient(s) and may > contain confidential and privileged information. Any unauthorized review, > use, disclosure or distribution is prohibited. If you are not the intended > recipient, please contact the sender by reply email and delete and destroy > all copies of the original message, including attachments. > > > > > > > > > -- -Achilleus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] mail + rfc822, rfc2822 + schema
O Ron Peterson έγραψε στις Jan 18, 2005 : > On Tue, Jan 18, 2005 at 05:05:55PM +0200, Achilleus Mantzios wrote: > > > i am thinking of doing a remote MUA web-based system, based on > > postgresql. > > ... > > So, i'd like to know if any of you has designed a schema serving as an > > mail storage. If anything of caution arrose to you, and if you had > > something to recommend. > > You might want to look at dbmail. > http://www.dbmail.org/index.php?page=overview Thanx, i also found http://yukatan.sourceforge.net/ which seems pretty intuitive and well designed. > > -- -Achilleus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] mail + rfc822, rfc2822 + schema
Hi, i am thinking of doing a remote MUA web-based system, based on postgresql. The idea is to let uucp/sendmail do the job of transporting the smtp mail to the remote site, and then using an mbox parser at the site (i have already done that in java), to offload (remotely) the mail messages to the remote postgresql. (I didnt want an IMAP, POP3 solution since POP3 dont make no sense at all since the web and mail server will be the same machine, and IMAP adds administrative overhead and deviates from our wierdish custom J2EE jboss/postgresql authentication mechanism, plus it gives features that we dont want at all - shared folders, etc..). So since we want the email system to fit seemlessly into the rest of the system, just like yet another app, the obvious was to think of an postgresql based Store. So, i'd like to know if any of you has designed a schema serving as an mail storage. If anything of caution arrose to you, and if you had something to recommend. By default i'll create a schema sufficient enuf to hold as much as the javax.mail.internet.MimeMessage can give, and the attachments. Thanx. -- -Achilleus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Get current trasanction id
O Marek Lewczuk έγραψε στις Dec 27, 2004 : > Hello, > is there any way to get current transaction id using plpgsql or sql ? Maybe write a C function which calls GetCurrentTransactionId(). > > Thanks in advance for any help. > > ML > > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > -- -Achilleus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Date datatype
O Pablo Digonzelli έγραψε στις Dec 20, 2004 : > hi all, > Ca anyone send me an example how to update a date datatype? > for example > update table set birthdate = "200-01-01" doesnt work. # update table set birthdate = '2000-01-01'; > > TIA > Pablo > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- -Achilleus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [despammed] [SQL] sql can i substitute
O Kenneth Gonsalves έγραψε στις Dec 17, 2004 : > On Friday 17 December 2004 05:54 pm, Andreas Kretschmer wrote: > > > Yes, simple: > > > > test=# select name, case > > test-# when fruit = 1 then 'good' > > test-# when fruit = 2 then 'bad' > > test-# when fruit = 3 then 'rotten' end as fruit from fruit; > > can one do the same thing for an 'insert' statement? its an expression, so yes, INSERT INTO foo3 VALUES (2,case when 't' then 'bar' else 'foo' end); but whats the point? > > kg > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > -- -Achilleus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Failed system call was shmget(key=1, size=1155072, 03600).
O Andrew M έγραψε στις Dec 3, 2004 : > Hi, > when building postgreSQL 8 I get the following error message: > > DETAIL: Failed system call was shmget(key=1, size=1155072, 03600). > HINT: This error usually means that PostgreSQL's request for a shared > memory segment exceeded available memory or swap space. To reduce the > request size (currently 1155072 bytes), reduce PostgreSQL's > shared_buffers parameter (currently 50) and/or its max_connections > parameter (currently 10) > > What is the best way to resolve this? max_connections = 10? Does that > figure auto increase as more users request data? > > regards > > Andrew This has been addressed many times, and for many OS's (*Linux, *BSD, etc...). Check the archives. > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- -Achilleus ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] get sequence value of insert command
O Erik Thiele έγραψε στις Nov 19, 2004 :
> hi
>
> create sequence mysequence;
>
> create table foo(
> id integer default nextval('mysequence'),
> bla text,
> wombat integer,
> foobar date,
> primary key(id)
> );
>
> insert into foo (wombat) values (88);
>
> now how do i know the id of my newly inserted element? and
> how can this be done in a completely concurrency safe way?
The way to do this is by reading the docs :)
use currval, it is session safe.
>
>
> cya
> erik
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
>
--
-Achilleus
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] session_id
O Richard Huxton έγραψε στις Nov 17, 2004 :
> Riccardo G. Facchini wrote:
> > hi all,
> >
> > is there a way to determine the session id on a database session?
> >
> > I would need to have a unique number whenever a session is started, and
> > have this available as a function or view result.
Why not SELECT pg_backend_pid();
??
>
> Add a new sequence to your database:
>CREATE SEQUENCE my_session_id;
>
> Then, at the start of every session:
>SELECT nextval('my_session_id');
>
> and whenever you need the value:
>SELECT currval('my_session_id');
>
> Sequences are concurrency-safe, so you're OK with multiple clients. They
> return INT8 values, so you should be good for unique numbers for a while.
>
> The only thing is, you need to remember to call nextval() every time you
> connect.
>
> HTH
>
--
-Achilleus
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] tree structure photo gallery date quiery
O Oleg Bartunov έγραψε στις Nov 17, 2004 :
> Gary,
>
> if you need really fast solution for you task and dont't afraid
> non-standard soltion, take a look on contrib/ltree module.
> http://www.sai.msu.su/~megera/postgres/gist/ltree/
>
> Oleg
Oleg how would you compare an ltree solution against a
genealogical approach using intarray??
i.e. store for each node, its path to root,
e.g. path='{4,2,7}'::int4[] where 4 is the id of the father,
2 of the grandfather, and 7 of the root, whereas root has
path is null.
That way we can do really fast queries using ~, have indexes
on level (path length),first (immediate ancestor),last (root)
using C functions that we can easily write and so forth.
I have extensively used this approach with success.
Can you comment on the pros and cons of each? (int[] with intarray vs
ltree).
Thanx
> _
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
>
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
--
-Achilleus
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Move table between schemas
O Andrew Sullivan έγραψε στις Nov 16, 2004 : > On Tue, Nov 16, 2004 at 10:02:34AM +0100, Markus Schaber wrote: > > Hello, > > > > Is there an easy way to move a table to another schema in PostgreSQL 7.4? > > > > ALTER TABLE and ALTER SCHEMA don't have this options. > > CREATE TABLE newschema.newtable AS SELECT * FROM oldschema.oldtable > > oughta work. What about indexes, constraints, sequences,etc...??? > > A > > -- -Achilleus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] A transaction in transaction? Possible?
O Peter Eisentraut έγραψε στις Nov 10, 2004 : > Achilleus Mantzios wrote: > > Wouldn't make more sense to allow nested begin/commit/rollback > > blocks? > > Possibly. But that consideration would have been more relevant about 6 > years ago when they wrote the SAVEPOINT syntax into the SQL standard. > :) In other words, now with savepoints, BEGIN; COMMIT; ROLLBACK; can be replaced with SAVEPOINT foo; RELEASE foo; ROLLBACK TO foo; respectively. If only transactions weren't a requirement for SAVEPOINTs, what would we then need BEGIN; COMMIT; ROLLBACK; for? > > -- -Achilleus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] A transaction in transaction? Possible?
O Michael Fuhr έγραψε στις Nov 10, 2004 :
> On Wed, Nov 10, 2004 at 12:45:19AM -0800, Riccardo G. Facchini wrote:
>
> > Sorry, but I understand that your example is not really about nested
> > transactions, but about sequential transactions.
>
> Here's a more elaborate example. If this doesn't demonstrate the
> capability you're looking for, then please provide an example of
> what you'd like to do and describe the desired behavior.
>
> CREATE TABLE person (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
>
> BEGIN;
> INSERT INTO person (name) VALUES ('Alice');
>
> SAVEPOINT s1;
> INSERT INTO person (name) VALUES ('Bob');
>
> SAVEPOINT s2;
> INSERT INTO person (name) VALUES ('Charles');
>
> SAVEPOINT s3;
> INSERT INTO person (name) VALUES ('David');
> ROLLBACK TO s3;
>
> INSERT INTO person (name) VALUES ('Edward');
> ROLLBACK TO s2;
>
> INSERT INTO person (name) VALUES ('Frank');
> RELEASE s1;
>
> INSERT INTO person (name) VALUES ('George');
> COMMIT;
Just a very naive thought
Wouldn't make more sense to allow nested begin/commit/rollback blocks?
>
> SELECT * FROM person;
> id | name
> +
> 1 | Alice
> 2 | Bob
> 6 | Frank
> 7 | George
>
> If you change "ROLLBACK TO s2" to "RELEASE s2" then you get this:
>
> id | name
> +-
> 1 | Alice
> 2 | Bob
> 3 | Charles
> 5 | Edward
> 6 | Frank
> 7 | George
>
> If you change "RELEASE s1" to "ROLLBACK TO s1" then you get this:
>
> id | name
> +
> 1 | Alice
> 7 | George
>
>
--
-Achilleus
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Drop all indexes of a table w/o knowing the index names
O Giulio Orsero έγραψε στις Nov 9, 2004 : > 7.4.6 on Linux. > > I need a way to drop all indexes of a table without knowing the names of the > indexes. > > Say I have a table > > table1 > index1 > index2 > index3 > > I don't want to do > > drop index1; > drop index2; > drop index3; > > but I want > > drop if your are using default namespace (schema) % tcsh % foreach i ( `psql -t -q -c "SELECT ci.relname from pg_index i,pg_class ci,pg_class ct where i.indexrelid=ci.oid and i.indrelid=ct.oid and ct.relname='YOUR_TABLE_HERE'"` ) foreach? psql -c "drop index $i" foreach? end > > is this possible? I looked in the manual at pg_index, but couldn't build an > sql string to do it. > > Thanks > > -- -Achilleus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] sql problem
O Flavio Fonseca έγραψε στις Nov 5, 2004 : > Hi, > > I am having a problem with a system I developed using php with postgres. > > Take a look at this: > > Welcome to psql 7.3.2, the PostgreSQL interactive terminal. > NetAdmin=# delete from operador where oplogin = 'ff'; > ERROR: fk_historicosessao_operador referential integrity violation - key in > operador still referenced from historicosessao > > and then, this: > > Welcome to psql 7.4.2, the PostgreSQL interactive terminal. > NetAdmin=# delete from operador where oplogin = 'ff'; > ERROR: update or delete on "operador" violates foreign key constraint > "fk_historicosessao_operador" on "historicosessao" > > I am not able to get a error_number in pgsql errors, only the error message. > So when I used pgsql 7.3 a used the string "referential integrity violation" > to detect this error, but on version 7.4 of pgsql this string was replaced > with "violates foreign key constraint" . > Anyone has a definitive solution on this or the way is to wait and change all > my code on future releases of pgsql? The right way to do so is to query for the SQLSTATE codes. For instance the jdbc7.4.6 driver supports sqlstate, which you can retrieve by sqle.getSQLState() That text of an Error may change across releases but the error code should be constant. p.s I dont do it my self. i just let my users educate themselves :) > > > Thank you all for the attention. > -- -Achilleus ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] 'show databases' in psql way?
O lorid έγραψε στις Nov 2, 2004 : > \z will list the tables in the dbase > \d tablename will list the columns in the table > or foodb=# SELECT oid,* from pg_database ; > - > Erik Wasser wrote: > > >Hi list, > > > >how can I list the databases in a postgresish way? I know about the '-l' > >switch of 'psql' but is there a DBI/SQL-query way? I don't want to call > >an external program only to list the databases. I've googled about this > >problem but I only found the '-l'-way to this this. > > > >Ideas? Solutions? > > > > > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > -- -Achilleus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Offtopic: psql
O sad έγραψε στις Oct 21, 2004 : > Hi > > Postgres has a perfect tool - psql > what libraries did you (developers) use to develop psql console and > particulary command-line editor. % ldd `which psql` maybe? > > thnx > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > -- -Achilleus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] help on a query
O CHRIS HOOVER έγραψε στις Oct 8, 2004 : > Just curious, what is wrong with the first way of coding the solution? > --( Forwarded letter 1 follows )- > Date: Fri, 8 Oct 2004 08:44:23 +0400 > To: Thomas.F.O'[EMAIL PROTECTED], [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > From: [EMAIL PROTECTED] > Sender: [EMAIL PROTECTED] > Subject: Re: [SQL] help on a query > > On Friday 08 October 2004 07:10, Thomas F.O'Connell wrote: > > A query that should get the job done is: > > > > SELECT registration_id > > FROM registrations r > > WHERE NOT EXISTS ( > > SELECT 1 > > FROM receipts > > WHERE registration_id = r.registration_id > > ); > > Don't, PLEASE, don't !!! > > drive this way : > > SELECT r.registration_id > FROM registrations AS r > LEFT OUTER JOIN receipts AS rec > ON rec.registration_id = r.registration_id > WHERE rec.registration_id IS NULL; Some one could add the same dont please dont, and advise: select registration_id FROM registrations where registration_id not in (select registration_id from receipts); but its actually version/data dependent i suppose. > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > -- -Achilleus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
