Re: [GENERAL] SSDs with Postgresql?
On 14/04/11 23:25, Vick Khera wrote: On Thu, Apr 14, 2011 at 12:19 AM, Benjamin Smith mailto:li...@benjamindsmith.com>> wrote: I was wondering if anybody here could comment on the benefits of SSD in similar, high-demand rich schema situations? For the last several months, I've been using Texas Memory Systems RamSAN 620 drives on my main DB servers. Having near zero seek times has been a tremendous boon to our performance, and will have pretty much paid for themselves within the next couple of months. Ie, the "throw hardware at it" solution worked really well :) hey, I wonder - could you, or someone else with some SSD drives running their DBs in production - check the SMART attributes for their drives? In particular, the Media_Wearout_Indicator - this starts at 100 and goes down towards 1 as the erase cycles add up.. So you can calculate the total estimated lifetime by looking at how much has been used up over how long you've been using the drive in production. I have a very cheap 64GB consumer SSD used in a personal server (so not in serious production use, but it does see some traffic), and I note that after a year it's still on 100%! Toby -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Multiple instances with same version?
Dear Everybody! I want to ask that have some way to install PGSQL 9.0 as two instances in one machine? Most important question. The OS is can be Windows or Linux. I asked this, because formerly we heard about a story. I cite this as I remember: We have some product, and in the only one server of the customer (Win) have a PG9.0 version installed. But the developer company lost in the space, only the software (must) working... We don't know the root password, and we don't want to hack it (the system must work). But we needed to install the our version of the PG what is also 9.0 (because of the new functions)... ... We want to prepare to same situations with learn about PG. With Firebird and MS-SQL this case is not problem, because on same machine we can install another instances with same version. But I don't know that is PG supports multiple instances with same version or not? Also interesting question are the "users". In our systems we create user for every real user. If they are 100, we have same number of db users. But if we want to server more database in one place, we may do conflict on users. For example: all of the databases have user JohnM. If we can do multiple instances, the problem is vanishing, because all have it's own user list. If we cannot, then only idea if have if we make prefix on usernames based on short dbname. For example: offer_db users: off_JohnM, off_MaryK press_db users: prs_JohnM, prs_TomR Please help just a little to I can know the limitations of PG. Thanks for it! Regards: dd
Re: [GENERAL] SSDs with Postgresql?
On 20/04/11 04:28, Yeb Havinga wrote: On 2011-04-19 19:07, Benjamin Smith wrote: On Sunday, April 17, 2011 01:55:02 AM Henry C. wrote: > > Exactly. Be aware of the risks, plan for failure and reap the rewards. Just curious what your thoughts are with respect to buying SSDs and mirroring them with software RAID 1. (I use Linux/CentOS) Since SSD fail when the write cycles are gone, it wouldn't make sense to buy two identical ones and put them in a RAID 1: under normal circumstances they'd fail simultanously. I don't think SSD drives wear out in quite the manner you seem to describe. The wearing out of blocks is not something that occurs at an exact number; the quoted amounts are more of an average, and will vary from block to block and from drive to drive. Also, all decent SSD drives will remap bad blocks as they wear out, so you don't get just one and then die. Also, the number of erase cycles you can get, over the whole disk, is quite large on modern disks! So large that you'll probably go decades before you wear the disk out, even with continual writes. Don't buy into the SSD FUD myths.. Cheers, Toby -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Transport Compression (whatever became of that discussion?)
In this discussion there was a lot of talk of transport compression in Postgres, (also specifically wondering about JDBC as well) did anything ever come of that discussion? http://postgresql.1045698.n5.nabble.com/Compression-on-SSL-links-td2261205.html
[GENERAL] Re: Are Update rights on the target table of a do instead update rule necessary ?
Hi, Apologies for flooding the list. I was updating the table when I should update the view. That's the price for working after a regular day job. Regards, Geraldo Lopes de Souza 2011/4/16 Geraldo Lopes de Souza > Hi, > > I'm trying to implement tenant view filter with postgres. The docs says > > "Rewrite rules don't have a separate owner. The owner of a relation (table > or view) is automatically the owner of the rewrite rules that are defined > for it. The PostgreSQL rule system changes the behavior of the default > access control system. Relations that are used due to rules get checked > against the privileges of the rule owner, not the user invoking the rule. > This means that a user only needs the required privileges for the > tables/views that he names explicitly in his queries." > > Postgres 9.0.3 > > I can confirm that on insert and delete rules: (do nothing ones ommited) > > create rule tnt_operadora_insert as > on insert to tnt_operadora > where new.tenant_id = current_tenant() > do instead > insert into operadora (id, tabeladecobranca, versaodoxml, nome, > numeronaoperadora, testedouble, registroans, "version", tenant_id) > values (new.id, new.tabeladecobranca, new.versaodoxml, new.nome, > new.numeronaoperadora, new.testedouble, new.registroans, new.version, > new.tenant_id); > > create rule tnt_operadora_del as > on delete to tnt_operadora > where old.tenant_id=current_tenant() > do instead > delete from operadora > where tenant_id=old.tenant_id and > id=old.id; > > > the view is tnt_operadora is a proxy for operadora table and to insert into > or delete from this view the user needs privileges to the view only docs > says. > > GRANT SELECT,INSERT,UPDATE,DELETE ON TNT_OPERADORA TO PUBLIC; > > For update rule that's not the case: > > create rule tnt_operadora_upd as > on update to tnt_operadora > where old.tenant_id = current_tenant() and > new.tenant_id = old.tenant_id > do instead > update operadora > set > tabeladecobranca = new.tabeladecobranca, > versaodoxml = new.versaodoxml, > nome = new.nome, > numeronaoperadora = new.numeronaoperadora, > testedouble = new.testedouble, > registroans = new.registroans, > "version" = new."version" > where > tenant_id = old.tenant_id and > id = old.id; > > Unless the user has update rights on the target table operadora I get: > > /opt/PostgreSQL/9.0/bin/psql clinica_dev tnt1 -f upd.sql > Password for user tnt1: > psql:upd.sql:3: ERROR: permission denied for relation operadora > > upd.sql: > update operadora > set tabeladecobranca= 'new value' > where id=83 and tenant_id=1 > > Further details: > > The purpose of these rules is to limit application code activities to the > records that belong's to the ordinary user representing the tenant, that is > intercepted through current_tenant() function. > > create domain tenant_id integer not null; > > create table tenant ( > id tenant_id primary key, > nome text not null, > email text > ); > > create or replace function current_tenant() returns tenant_id as $$ > begin > if substring(current_user,1,3) = 'tnt' then > return cast( substring(current_user,4,10) as integer); > else > return null; > end if; > end > $$ language plpgsql > > create or replace view public.tnt_operadora as > select * from public.operadora > where tenant_id=current_tenant(); > > > Thank you very much, > > Geraldo Lopes de Souza >
Re: [GENERAL] Needs Suggestion
On Tue, Apr 19, 2011 at 5:06 PM, SUBHAM ROY wrote: > Suppose Postgres is installed in two computers C1 & C2. > C1 have some database tables. How can I copy these database tables from C1 > to C2. > I mean to say that can I copy tables from postgres installed in one m/c to > another m/c. > Is there any command in postgres to do so or any other short cut technique. Assuming you don't have to worry about foreign keys etc, you can dump a table from one db and feed it to a psql session open to another: pg_dump -h host1 dbname -t tablename | psql -h host2 dbname and so on. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Needs Suggestion
Suppose Postgres is installed in two computers C1 & C2. C1 have some database tables. How can I copy these database tables from C1 to C2. I mean to say that can I copy tables from postgres installed in one m/c to another m/c. Is there any command in postgres to do so or any other short cut technique. -- Thank You, Subham Roy, CSE IIT Bombay.
Re: [GENERAL] Using column aliasses in the same query
On 18 April 2011 22:06, Tore Halvorsen wrote: > Well, refering to the computed value may be nonsensical, but > couldn't it be some sort of query rewrite? So that... > > SELECT x/y AS z FROM tab WHERE y <> 0 AND z > 2 > > ... is a shorthand for > > SELECT x/y AS z FROM tab WHERE y <> 0 AND x/y > 2 > > No big deal, since there are lots of other ways to do this. That's an accurate observation, but has nothing to do w/ what the original poster was looking for, nor does it refute Toms argument against the OPs suggestion. Cheers, Andrej -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Questions about Partitioning
On 04/19/2011 08:56 AM, Phoenix Kiula wrote: While I fix some bigger DB woes, I have learned a lesson. Huge indexes and tables are a pain. Which makes me doubly keen on looking at partitioning. Before jumping into partitioning it would be useful to know specifically what pain you are having with your current tables and indexes. Maintenance? Performance? Other? Question zero is "What issues are currently causing you pain with large tables?" and after that determining if the partitioning is an appropriate solution. There is pain associated with partitioning, as well, so you need to be sure that you will achieve a net pain reduction. Carefully read http://www.postgresql.org/docs/current/static/ddl-partitioning.html, it has examples that answer several of your questions. Pay extra attention to 5.9.6 "Caveats". Some places where partitioning work well: 1. The partition can substitute for an index and the resulting child tables will have somewhat comparable sizes. If you had contact information where state was typically required in queries you might partition the data into tables for each state so a typical query would only touch a smaller data set and the partitioning/child-table constraints substitute for an index on state. 2. You frequently drop data in bulk and can group that data in such a way that you can drop or truncate a child-table. Among the places I've used partitioning is for validation codes. I partition them by like expiration and when the date arrives, I just drop the partition with the expired codes - way faster than delete-from and the necessary follow-up maintenance when deleting millions of codes. 3. The nature of your data is such that it can be partitioned into a small part that is accessed frequently and parts that are relatively rarely accessed. Most examples I see online are partitioned by date. As in months, or quarter, and so on. This doesn't work for me as I don't have too much logic required based on time. Time-based data often satisfies all of the above (log data you can partition by month, typically only look at the current month and drop data that is a year old, for example) so that's what ends up being in most examples. The biggest, highest volume SELECT in my database happens through an "alias" column. This is an alphanumeric column. The second-biggest SELECT happens through the "userid" column -- because many users check their account every day. A rough table definition can be considered as follows: CREATE TABLE maintable idSERIAL primary key alias VARCHAR(42) ... user_id VARCHAR(30) user_registered BOOLEAN statusVARCHAR(1) My questions: 1. Which column should I partition by -- the "alias" because it's the largest contributor of queries? This should be OK, but my concern is that when user_id queries are happening, then the data for the same user will come through many subtables that are partitioned by "alias" -- will this happen automatically (presuming "constraint exclusion" is on)? How does partitioning by one column affect queries on others. Will there be subtable-by-subtable indexes on both alias and Answer question zero, above, first. But beware - the primary key is not inherited. You run the risk of duplicating the primary key (or other unique identifier) across child tables unless you implement the appropriate constraints on the child tables to prevent this. It's also pointless to have a primary key on the parent table in most situations. 2. How does SERIAL type work with partitions? Will INSERT data go into the respective partitions and yet maintain an overall sequence -- I mean, the *same* overall sequence for the parent table distributed automagically across subtables? This depends on how you set up your triggers, constraints, child tables etc. but by default a basic "create table thechild () inherits (theparent);" will result in a child table that shares the same sequence as the parent. 3. If I partition using "a%", "b%" etc up to "z%" as the partition condition, is this an issue -- are about 26 subtables too many partitions? Mine are static partitions as in they will be the same forever, unlike data-based partitions. And each partition will continue to grow. If I include that "alias"es can begin with numbers and allowed symbols too, then this may be 45 partitions? What's the limit of partitions -- not only official limit, but practical limit in terms of performance? As always, the answer is "depends" but I wouldn't typically see 45 as too many. See primary-key warning above. It's less an absolute number of tables and more whether the design of your tables and queries results in execution efficiency gains that outweigh the additional planner costs. 4. Given that it's a wildcard LIKE condition (with a "%") will this affect the index and subsequent SELECT speed? Are partition conditions recommended to be "=" or "<" t
Re: [GENERAL] setting connection/ query timeout
On Tue, Apr 19, 2011 at 8:09 PM, tamanna madaan wrote: > Sometimes this query gets stuck for unknown reason . So, for this particular > query I want to set a timeout > . I dont want to change "statement_timeout" in postgresql.conf as this would > affect all the connections/queries. > I just want to set timeout for above mentioned query . How can I do that ?? You can set a timeout only valid for the session using SET: => set statement_timeout to 1000; SET => select pg_sleep(2); ERROR: canceling statement due to statement timeout -- Daniele -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] setting connection/ query timeout
Hi All I am using postgres-8.1.2 . In postgresql.conf I have set : #statement_timeout = 0 I am executing the below query just to check if postgres is allowing connections or not . psql -Uslon -d -h -c "select 1;" Sometimes this query gets stuck for unknown reason . So, for this particular query I want to set a timeout . I dont want to change "statement_timeout" in postgresql.conf as this would affect all the connections/queries. I just want to set timeout for above mentioned query . How can I do that ?? Moreover, Just in case there is some problem while getting connection to postgres while executing the above query , I want the query to exit . How can I set connection timeout for that ?? Thanks.. Tamanna
Re: [GENERAL] "Service user account 'postgres' could not be created" -- 8.4.2-1 and Active Directory on Windows08 R2
On 04/19/11 11:48 AM, Greg Corradini wrote: Hello, I'm getting 'service user account 'postgres' could not be created' when using the postgresql-8.4.2-1 one-click installer (i also get the same error with a new version of postgresql 8.4.8-1) So i know in the past (version 8.3.x) there were problems with Windows Server 2008 R2 running Active Directory and postgresql around this same error. I'm running into exactly the same errors as described here: http://forums.enterprisedb.com/posts/list/1516.page After I get the error i cancel out of the installation, make sure there's no postgresql install, make sure there's no postgresql service running, blow away postgres user, make sure there is nothing in the registry and reboot. I try to reinstall again and run into the same problem. is this server a domain controller or just a member of the domain? if its a domain controller, you'll need to create a domain account 'postgres', configuure this with MINIMUM privileges, just 'run as service' on the host, nothing else (these are configured in security policies). give this account a arbitrary complex password. then, when the postgres installer is running, if it asks you about the account, tell it to use DOMAINNAME\postgres and that password you assigned, and it should be happy. if its NOT a domain controller, create a account 'postgres' via Administration Tools -> Computer Management -> Local Users and Groups -> Users... remove the "Users' group from this account, and in Local Security Policies (also in Computer Management), make sure the account COMPUTERNAME\postgres is listed under 'Run as Service', then do the same as above, only specify COMPUTERNNAME\postgres as the account. hope this helps? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "Service user account 'postgres' could not be created" -- 8.4.2-1 and Active Directory on Windows08 R2
With R2, I had to install using the admin account. I got those errors before. It worked once I used a admin account. If that doesn't work you might want to try turning UAC off, do the install and then turn back on. Best Regards Michael Gould "Greg Corradini" wrote: >Hello, >I'm getting 'service user account 'postgres' could not be created' when >using the postgresql-8.4.2-1 one-click installer (i also get the same >error with a new version of postgresql 8.4.8-1) > >So i know in the past (version 8.3.x) there were problems with Windows >Server 2008 R2 running Active Directory and postgresql around this same >error. I'm running into exactly the same errors as described here: >href="http://forums.enterprisedb.com/posts/list/1516.page";>http://forums.enterprisedb.com/posts/list/1516.page > > >After I get the error i cancel out of the installation, make sure there's >no postgresql install, make sure there's no postgresql service running, >blow away postgres user, make sure there is nothing in the registry and >reboot. > >I try to reinstall again and run into the same problem. > >-- >Greg > > -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
[GENERAL] "Service user account 'postgres' could not be created" -- 8.4.2-1 and Active Directory on Windows08 R2
Hello, I'm getting 'service user account 'postgres' could not be created' when using the postgresql-8.4.2-1 one-click installer (i also get the same error with a new version of postgresql 8.4.8-1) So i know in the past (version 8.3.x) there were problems with Windows Server 2008 R2 running Active Directory and postgresql around this same error. I'm running into exactly the same errors as described here: http://forums.enterprisedb.com/posts/list/1516.page After I get the error i cancel out of the installation, make sure there's no postgresql install, make sure there's no postgresql service running, blow away postgres user, make sure there is nothing in the registry and reboot. I try to reinstall again and run into the same problem. -- Greg
Re: [GENERAL] SSDs with Postgresql?
On 2011-04-19 19:07, Benjamin Smith wrote: On Sunday, April 17, 2011 01:55:02 AM Henry C. wrote: > > Exactly. Be aware of the risks, plan for failure and reap the rewards. Just curious what your thoughts are with respect to buying SSDs and mirroring them with software RAID 1. (I use Linux/CentOS) Since SSD fail when the write cycles are gone, it wouldn't make sense to buy two identical ones and put them in a RAID 1: under normal circumstances they'd fail simultanously. An idea I'm thinking of is to put both a OCZ Vertex 2 Pro and Intel 320 in a software RAID 1 setup. It would have the benefit that there are no complications to be expected with an extra device layer from a hardware RAID card, such as incompatibilites between controller and SSD firmware, or not being able to access the physical disk's smart values, which in the SSD case are important to figure out the remaining lifetime. Also since both drives have a supercap, PostgreSQL data on it would survive power failures. It would be interesting if md could be configured to do reads on both mirror legs and compare them before returning values, like a continual check, but there doesn't seem to be a option for something like that. -- Yeb Havinga http://www.mgrid.net/ Mastering Medical Data
[GENERAL] If block half-executing even though condition is false
I'm getting some really weird behavior in a function I swear was working a couple of weeks ago. For reasons I do not agree with, the database our main application relies on stores times without time zones. Instead, we store each time twice, once as a timestamp containing wallclock time and the other as a timestamp containing UTC time. When we need to calculate intervals across time zones, we have to handle the time change ourselves. We haven't been doing a very good job of it. I came up with a function that handles it. I was running final tests on it, and suddenly it wasn't working any more. The function contains this code: raise notice 'Answer should be: % but FinishTime is %', StartingTime + RequiredInterval, FinishTime; --return FinishTime; if extract(timezone from StartingTime) = StandardOffset and extract(epoch from UTCDifference) = DaylightOffset then raise notice 'Ambiguous time was recorded in daylight savings time.'; FinishTime := FinishTime - interval '1 hour'; end if; --raise notice 'Charge % started current phase at % and will finish at %.', -- ChargeNum, StartingTime, FinishTime; return FinishTime; The starting time is 3/13/11 00:00, two hours before the change from standard to daylight savings time, and the required interval is 48 hours. The answer should be 3/15/11 01:00-04. The if block should be executed only if the wallclock time is in the interval of 1-2 AM on the day of the fall time change, when that hour gets repeated. The first raise notice statement tells me that FinishTime is 1 AM on the 15th, as expected. But the returned value is midnight. If I comment out the line in the if block that takes an hour off the FinishTime, the returned value is 1 AM as expected. But the raise notice message does not appear in my Messages window! I would understand this if the if block was just a single statement, such as would be true in C++ if I omitted the braces surrounding the block. But in PG/PSQL, the if block is delimited by the "end if;" line. So what is going on with this function? If you need more information (such as the entire function), please ask. RobR
Re: [GENERAL] SSDs with Postgresql?
On Sunday, April 17, 2011 01:55:02 AM Henry C. wrote: > On Thu, April 14, 2011 18:56, Benjamin Smith wrote: > > After a glowing review at AnandTech (including DB benchmarks!) I decided > > to spring for an OCX Vertex 3 Pro 120 for evaluation purposes. It cost > > about $300 > > > > with shipping, etc and at this point, won't be putting any > > > > Considering that I sprang for 96 GB of ECC RAM last spring for around > > $5000, even if I put the OCX drives in pairs w/RAID1, I'd still come out > > well ahead if it allows me to put off buying more servers for a year or > > two. > > Exactly. Be aware of the risks, plan for failure and reap the rewards. Just curious what your thoughts are with respect to buying SSDs and mirroring them with software RAID 1. (I use Linux/CentOS) -Ben -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
[GENERAL] Questions about Partitioning
While I fix some bigger DB woes, I have learned a lesson. Huge indexes and tables are a pain. Which makes me doubly keen on looking at partitioning. Most examples I see online are partitioned by date. As in months, or quarter, and so on. This doesn't work for me as I don't have too much logic required based on time. The biggest, highest volume SELECT in my database happens through an "alias" column. This is an alphanumeric column. The second-biggest SELECT happens through the "userid" column -- because many users check their account every day. A rough table definition can be considered as follows: CREATE TABLE maintable idSERIAL primary key alias VARCHAR(42) ... user_id VARCHAR(30) user_registered BOOLEAN statusVARCHAR(1) My questions: 1. Which column should I partition by -- the "alias" because it's the largest contributor of queries? This should be OK, but my concern is that when user_id queries are happening, then the data for the same user will come through many subtables that are partitioned by "alias" -- will this happen automatically (presuming "constraint exclusion" is on)? How does partitioning by one column affect queries on others. Will there be subtable-by-subtable indexes on both alias and 2. How does SERIAL type work with partitions? Will INSERT data go into the respective partitions and yet maintain an overall sequence -- I mean, the *same* overall sequence for the parent table distributed automagically across subtables? 3. If I partition using "a%", "b%" etc up to "z%" as the partition condition, is this an issue -- are about 26 subtables too many partitions? Mine are static partitions as in they will be the same forever, unlike data-based partitions. And each partition will continue to grow. If I include that "alias"es can begin with numbers and allowed symbols too, then this may be 45 partitions? What's the limit of partitions -- not only official limit, but practical limit in terms of performance? 4. Given that it's a wildcard LIKE condition (with a "%") will this affect the index and subsequent SELECT speed? Are partition conditions recommended to be "=" or "<" type operators only or is LIKE ok?? 5. Does partitioning need to happen only through one column? Can I have a condition containing two columns instead? CREATE TABLE subtable_a ( PRIMARY KEY (id) CHECK ( user_id LIKE 'a%' and user_registered IS TRUE) ) INHERITS (maintable); CREATE TABLE subtable_b ( PRIMARY KEY (id), CHECK ( user_id LIKE 'b%' and user_registered IS TRUE) ) INHERITS (maintable); ..etc 6. Triggers - how do they affect speed? Everything, insert, update, select will happen through this conditional trigger. I will likely be writing this in PLSQL, but I read in several websites that C triggers are much faster than PLSQL triggers. Is this a concern? 7. "Constraint exclusion" - is it recommended to have this in the pg.conf, or will I need to do this before every SQL? I prefer the pg.conf way, but want to confirm that there are no downsides for other regular SQL operations with this setting? 8. How will JOIN work? I have different tables JOINing with the parent table now. With partitioned subtables, will constraint exclusion automatically do what's needed and my SQL does not need to change? Or will there be triggers required for each and every query I currently have? Eight questions is enough for my first post in this partitioning thread :) Thanks much! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to force an insert before Raise Exception?
On Tue, Apr 19, 2011 at 4:39 PM, giova wrote: > My problem is that i want to do an INSERT into a log table before to raise > the exception. But RAISE EXCEPTION cancels my Insert. That's the point of transaction, if it failed the data and any other changes are rolled back. You can use dblink (http://www.postgresql.org/docs/current/static/dblink.html), which will issue a separate connection to database to simulate autonomous transaction. Regards, Taras Kopets -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to force an insert before Raise Exception?
On Tuesday, April 19, 2011 6:39:23 am giova wrote: > Hi. > > I made a function that Raise exception with some conditions. > No problem with that, it is the goal. > > My problem is that i want to do an INSERT into a log table before to raise > the exception. But RAISE EXCEPTION cancels my Insert. > > How to force the insert to not being cancelled please? > Note that i want my exception to be raised , so i can't use: > EXCEPTION WHEN THEN Why not? From the docs: "When an error is caught by an EXCEPTION clause, the local variables of the PL/pgSQL function remain as they were when the error occurred, but all changes to persistent database state within the block are rolled back. As an example, consider this fragment: " And instead of RAISE EXCEPTION use RAISE NOTICE in the exception clause. > > Thanks for your help. > -- Adrian Klaver adrian.kla...@gmail.com
[GENERAL] how to force an insert before Raise Exception?
Hi.
I made a function that Raise exception with some conditions.
No problem with that, it is the goal.
My problem is that i want to do an INSERT into a log table before to raise
the exception. But RAISE EXCEPTION cancels my Insert.
How to force the insert to not being cancelled please?
Note that i want my exception to be raised , so i can't use:
EXCEPTION WHEN THEN
Thanks for your help.
example :
CREATE OR REPLACE FUNCTION "PrepareTrialLic"(userid integer)
BEGIN;
IF userid = 0 THEN
--I want to force that insert !!
INSERT INTO log_error(caller, description) VALUES('PrepareTrialLic',
'userid is 0');
RAISE EXCEPTION 'userid can''t be equal to 0';
ENDIF;
END;
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-force-an-insert-before-Raise-Exception-tp4313283p4313283.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_reorg
On Tue, Apr 19, 2011 at 8:48 AM, Jens Wilke wrote: > On Tue, Apr 19, 2011 at 04:02:01AM +0530, Vibhor Kumar wrote: > >> > IIRC "vacuum full" mode rewrites the indexes as well. >> >> Till 8.4 no. From 9.0 onwards yes. However VACUUM FULL still locks the table. > > Don't be confused with the "vacuum full" term. > This has nothing to do with the postgresql "vacuum full" command. > Both pg_reorg's "vacuum full" and "cluster" mode do the pretty same thing. > They rewrite the table and all their indexes. They use triggers to update the > new table during the reorganisation. > The only difference is that "cluster" does an additional order by. pg_reorg allows you to do natural ordering (meaning, no defined ordering), or define any ordering you like, so it's in fact far superior to cluster in that sense. Natural ordering is the fastest and should complete faster than cluster. If you've ever contemplated using triggers to stage data to a table temporarily while locking and going to town on a large bloated table, then pg_reorg is for you -- that's more or less what it does. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_reorg
On Tue, Apr 19, 2011 at 04:02:01AM +0530, Vibhor Kumar wrote: > > IIRC "vacuum full" mode rewrites the indexes as well. > > Till 8.4 no. From 9.0 onwards yes. However VACUUM FULL still locks the table. Don't be confused with the "vacuum full" term. This has nothing to do with the postgresql "vacuum full" command. Both pg_reorg's "vacuum full" and "cluster" mode do the pretty same thing. They rewrite the table and all their indexes. They use triggers to update the new table during the reorganisation. The only difference is that "cluster" does an additional order by. Both of them lock the original table at the end of the reorganisation just for the switch. If the lock is not granted within -T seconds, the backends holding locks are canceled. If you run out of diskspace, it's possible to reorg table by table. And yes, pg_reorg does only work with tables with a primary key. This will change in future releases, IIRC regards, Jens -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] create table sintax
2011/4/19 Júlio Almeida
> If I run
> create table newtable (like oldtable including constraints);
> in the SQL window with works just file.
> But if i execute
> execute 'create table '||newtable||' (LIKE '||oldtable||' including
> constraints)';
> inside a function, in a LOOP, the constraints aren't created.
> What is the problem?
You probably missing something in your function.
Please find a simple example that shows how to create a table like
other table using function.
Remember to use *quote_ident* function when you use database
identifiers in dynamic SQL's.
BEGIN;
CREATE TABLE original (
a integer,
b text,
c date,
CONSTRAINT orginal_ck_text_has_dog CHECK (position('dog' in b) <> 0),
CONSTRAINT orginal_ck_date_is_recent CHECK (c >= '2000-01-01'::date)
);
insert into original(a, b, c) VALUES(1, 'hot dog', now());
/* error, as expected
insert into original(a, b, c) VALUES(2, 'hot cat', now());
*/
-- function to copy other table with constraints
CREATE OR REPLACE FUNCTION create_table_like(p_orig_table text,
p_new_table text)
RETURNS VOID AS $BODY$
BEGIN
EXECUTE 'CREATE TABLE '||quote_ident(p_new_table)
||' (LIKE '||quote_ident(p_orig_table)||' including constraints)';
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT;
-- execute function to create a new table like old one
SELECT create_table_like('original'::text, 'copied'::text);
insert into copied(a, b, c) VALUES(1, 'hot dog', now());
/* error, as expected
insert into copied(a, b, c) VALUES(2, 'hot cat', now());
*/
-- ROLLBACK;
Hope this helps,
Taras Kopets
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to configure a read-only database server and session? (Was: read-only UNLOGGED tables)
> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Stefan Keller > Sent: Sunday, April 17, 2011 2:04 PM > To: pgsql-general List > Subject: [GENERAL] How to configure a read-only database server and > session? (Was: read-only UNLOGGED tables) > > I have a single-disk virtual Linux system and a read-only dataset > which is exposed to internet and completely replaced from time to > time. > > I compiled following steps in order to secure and speedup such > PostgreSQL/PostGIS instance: > > 1. Re-configure PostgreSQL server as following: > > a. Disabling autovacuum daemon. > b. Setting postgresql.conf parameters: > fsync=off > synchronous_commit=off > full_page_writes=off > > 2. Restart server, login as db admin, create database, create an app.- > user. > > 3. Load dataset...: > a. with owner 'app.-user' in schema PUBLIC; > b. create indexes; > c. issue a VACUUM ANALYZE command on user tables. Might consider setting your indexes to be fill factor 100 if you have not already. Be aware of what this will mean when you "load dataset" in the future for a refresh. - Mark -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Feature request psql
Hallo, i have a possible feature request. It is not possible to get the sql statement itself and the output of the statement in a user-friendly way to an output file (option \o psql), this is only possible outside of psql, when calling this function with an option -L (psql -L log.txt). The nice thing about the option \o is that u can use multiple output files with different markups in 1 script. An example with \o file1 option one can only get in file1 " ECHO queries SET SET cid lid seq status lang payload DM SEL 1 None NL inleiding " while having at the same time in logfile (option psql -L logfile) " * QUERY ** SET SESSION AUTHORIZATION test; ** SET * QUERY ** SET search_path TO elswim; ** SET * QUERY ** SELECT * FROMget_overview(); ** cid | lid | seq | status | lang |payload -++-++--+--- DM | SEL| 1 | None | NL | inleiding (1 row) " basically, the echo command doesnt get written to the file1 (of \o file1) mvg, Wim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] REASSIGN OWNED doesn't for all objects, ALTER FUNCTION seems to fix it
Hi Tom, Op maandag 18 april 2011, schreef Tom Lane: > Hmmm look into pg_shdepend to see if there are entries linking > those functions to an owner. mmm, indeed it seems that some things are our of sync here the following is coming from the production database, thus after the 'reassign from A to postgres' was run 1. SELECT n.nspname as "Schema", p.proname as "Name", pg_catalog.pg_get_userbyid(p.proowner) as "Owner" FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang WHERE pg_catalog.pg_function_is_visible(p.oid) AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND pg_catalog.pg_get_userbyid(p.proowner) != 'postgres'; This returns all 60 functions that were not reassigned, 'Owner' here still is user 'A'. 2. select s.deptype, p.proname, pg_catalog.pg_get_userbyid(p.proowner) as proc_owner, pg_catalog.pg_get_userbyid(s.refobjid) as sh_dep_owner from pg_shdepend s full outer join pg_proc p on p.oid = s.objid where ( coalesce( (select datname from pg_database where oid = s.dbid) = 'megafox' and s.classid::regclass::text = 'pg_proc' and pg_catalog.pg_get_userbyid(refobjid) != 'postgres', false) or coalesce( pg_catalog.pg_get_userbyid(p.proowner) != 'postgres', false)) order by s.deptype, p.proname; This confirms that these 60 functions do not have a 'o' (owner) record in pg_shdepend, it therefor matches what you seemed to expect: no records in pg_shdepend, so "reassign owned" does not do anything. Our obvious questions now are: - how did we get into this and - how do we get out How is it possible that a function had a pg_catalog.pg_proc.proowner other than postgres while there are no corresponding records in pg_shdepend? Fyi, the last major upgrade (for which a pg_restore was done) was in july 2009. 3. The query above returns 10 other suspicious rows, suspicious to us at least. These rows are about functions which according to pg_catalog.pg_proc.proowner are owned by postgres (the last-but-one column), while in pg_shdepend they still have an 'o' record with owner 'A' (the last column). So again, pg_catalog.pg_proc.proowner and pg_shdepend are not in sync. - For what its worth, nothing special was noticed about postgresql nor the hardware. Postgresql for us has been and still is rock stable for almost ten years now ;) We did try some scenarios of changing ownership of things, but were not able to generate a situation with pg_proc.proowner not in sync with pg_shdepend. I guess this was to be expected, since a newly restored dump also does not show the problem, it's only in the production database, which ofcourse has moved through a number of minor upgrades without a restore. -- Best, Frank. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] create table sintax
Hello, If I run create table newtable (like oldtable including constraints); in the SQL window with works just file. But if i execute execute 'create table '||newtable||' (LIKE '||oldtable||' including constraints)'; inside a function, in a LOOP, the constraints aren't created. What is the problem? Tanks, julio almeida
