Re: [GENERAL] [Skytools-users] WAL Shipping + checkpoint
On 27/08/2009 18:11, Martin Pihlak wrote:
There are actually no real data changes being made on your master
for some reason. So every time archive_timeout is reached a log full
of no changes is shipped to your slave and applied - and no checkpoint
times are changed for reasons I mentioned above.
thanks, but we have not set archive_timeout, and we have a lot of real
data changes.
That's why i don't understand why checkpoint never happen on the slave.
What about the other values in pg_controldata output, do they change at
all? If they remain constant, maybe the logs are just not applied. Also,
are there any restored log file xxx from archive entries in postgres
log?
No, i don't see any change in pg_controldata, but :
there is messages in logfile :
2009-08-28 10:02:51,129 26717 INFO 000103F70088: Found
2009-08-28 10:02:51,169 26717 INFO {count: 1}
2009-08-28 10:02:51 CEST [18439]: [1862-1] user=,db= LOG: restored log
file 000103F70088 from archive
postg...@bdd2:/data/postgresql/8.3/main$ grep restored log file
/var/log/postgresql/postgresql-2009-08-28.log | wc -l
164
And I can see that the cluster is updated inside the directory px_xlog
and base, at least.
So we can think it's working, but, when i restart the slave cluster, it
process again all the WAL since the last checkpoint ( for nothing, I
think ), so, when we'll need this slave, it will take a lot of time,
more and more, and this point worried me a bit.
--
Sébastien Lardière
--
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] Viable alternatives to SQL?
On 27 Aug 2009, at 17:11, pgsql-general-ow...@postgresql.org wrote: From: Kelly Jones kelly.terry.jo...@gmail.com Date: 27 August 2009 14:43:51 BST To: pgsql-general@postgresql.org Subject: Viable alternatives to SQL? Many sites let you search databases of information, but the search queries are very limited. I'm creating a site that'll allow arbitrary SQL queries to my data (I realize I'll need to handle injection attacks). Are there other viable ways to query data? I read a little on Business System 12 (BS12), Tutorial D, and even something called T-SQL (I think), but they all seem theoretical and not fully implemented. I want a query language that non-techies can use easily, but also supports arbitrarily complex queries. Does such a language exist? Originally I was using the webexone.com database to allow non-techies to create tables and views, however found it too limiting - the functional ceiling too low. So our org. built a web-app for this based on postgres - a GUI. However it's not aimed at letting non-techies do everything, which is rather ambitious. People familiar with SQL and relational db concepts can do practically everything but non-techs can do simpler tasks. For example I recently used some of the new windowing functions (yay!) in a view but users typically add/remove fields, filters and aggregate calculations. Info at www.gtportalbase.com, it's also just gone on github as open source. Oliver Kohll
[GENERAL] pg_hba.conf problem in PostgreSQL 8.4 (no-installer)
Hello, I'm developing JSF web application in Java with Tomcat and PostgreSQL on the server. I use PostgreSQL 8.x NO INSTALLER version (zip file). Everything work fine until I moved from PostgreSQL 8.2 to PostgreSQL 8.4 - now I have problems with starting registered PostgreSQL service on Windows XP Prof or Windows Vist (Home Premium). The problem is connected with pg_hba.conf entries. Detailed description of the whole process: My Java code registers PostgeSQL service under Windows by executing command: C:\Program Files\MyApplication\database/bin/pg_ctl -D C:\Program Files\MyApplication\database\data register -N PostgreSQL_Service It works OK (I am doing this on local account with administrator privilleges, I have database cluster generated before) - the service is registered for Local Service Account. Now my Java code tries to start the service using command sc.exe start PostgreSQL_Service. And this fails. I would like to use ONE COMMON pg_hba.conf file for XP and Vista. When I had PostgreSQL 8.2 version, the file has those entries: # IPv4 local connections: hostall root 127.0.0.1/32 md5 # IPv6 local connections: hostallroot::1/32md5 And it WORKED OK for BOTH Windows XP and Vista. Now we changed PostgreSQL to version 8.4 (still no-installer version) and our pg_hba.conf file looks like this now: # IPv4 local connections: hostall all 127.0.0.1/32 md5 # IPv6 local connections: hostall all ::1/128 md5 When last line (IPv6) is present it, registered service does NOT start under Windows XP BUT WORKS under Vista (this XP windows have only IPv4 protocol and it is enabled, Vista have both IPv4 and IPv6 installed and enabled) - in the Windows Events Log I see error saying: FATAL: could not load pg_hba.conf. When last line is removed (or commented), registered service starts and WORKS under XP, BUT under VISTA I see error during startup saying about missing entry for host ::1. I don't use any antivirus/firewall during the test (it was tested under clean Windows installation, standard firewall was deactivated also). The questions are: 1. Is it possible to have such common pg_hba.conf file for XP and Vista 2. Why it worked OK under PostgreSQL 8.2 and under 8.4 it stopped working (is it somehow connected with fully parsing pg_hba.conf describe here: http://www.postgresql.org/docs/8.4/static/release-8-4.html#RELEASE-8-4-PG-HBA-CONF) Regards, Pawel -- 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] A safe way to upgrade table definitions by using ALTER's
On Fri, Aug 28, 2009 at 12:42:59AM +0400, Sergey Samokhin wrote:
But how do programmers guarantee that ALTER's they have wrote will
always be applied by administrators to the corresponding version of
the database?
How about using the normal integrity constraints that databases provide?
Have some table like:
CREATE TABLE version (
feature TEXT PRIMARY KEY,
depends TEXT REFERENCES version,
inserted TIMESTAMP DEFAULT now()
);
and at the start of every modification script put a row (or several)
into the table:
BEGIN;
INSERT INTO version (feature,depends) VALUES
('table foo',NULL);
CREATE TABLE foo ( id TEXT PRIMARY KEY, value TEXT );
COMMIT;
and then you can check to see if the constraints are met by doing:
BEGIN;
INSERT INTO version (feature,depends) VALUES
('table foo add startend dates','table foo');
ALTER TABLE foo
ADD COLUMN startdate TIMESTAMP DEFAULT now(),
ADD COLUMN enddate TIMESTAMP DEFAULT 'infinity';
COMMIT;
Not sure if that's the sort of thing that you want/need but I don't
think there's a general solution to the problem. Determining the
relevant context for this sort of thing is hard.
--
Sam http://samason.me.uk/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] details locks
Im work with postgres my questions is,its possible on postgres: for example procpidLocker object_locked user_lockedtypeof lock comand 1 admin fooadmin2 exclusive update... thnks for all Veja quais são os assuntos do momento no Yahoo! +Buscados http://br.maisbuscados.yahoo.com
Re: [GENERAL] GUI to edit a table's content
H.. I didn't see anything in http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools that stood out in terms of being able to edit table content in a GUI. Maybe MSACCESS, but my PG DB is served on Linux. I have googled around for something like this and was amazed at how little there is out there, at least in freeware. Perhaps this is an opportunity for me to develop something, but re-inventing the wheel here where I work is frowned upon. So I'm still looking. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Thomas Kellerer Sent: Thursday, August 27, 2009 5:32 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] GUI to edit a table's content Gauthier, Dave wrote on 27.08.2009 23:17: Are there any GUI apps out there that can be used to edit (insert, update, delete) table data? SOmething with enough smarts to sniff out constraint violations and report accordingly. Also, ability to eval constraints at commit time inside a transaction (option to rollback of course). Check out this list: http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Anybody know where to find Dan Langille?
... or whoever is responsible for buildfarm member grebe now? The owner address recorded in the buildfarm doesn't work: 550 5.1.1 dlangi...@afilias.info... User unknown regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GUI to edit a table's content
Gauthier, Dave, 28.08.2009 16:08: H.. I didn't see anything in http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools that stood out in terms of being able to edit table content in a GUI. Maybe MSACCESS, but my PG DB is served on Linux. The question is: what do you mean with edit in a GUI. e.g. with Squirrel or SQL Workbench/J you can run SELECT * FROM my_table and edit the contents of the result directly. Are you talking about something different? Thomas -- 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] Anybody know where to find Dan Langille?
On Fri, Aug 28, 2009 at 16:15, Tom Lanet...@sss.pgh.pa.us wrote: ... or whoever is responsible for buildfarm member grebe now? The owner address recorded in the buildfarm doesn't work: 550 5.1.1 dlangi...@afilias.info... User unknown He's d...@langille.org. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Data audit trail techniques in postgresql
Alvaro Herrera wrote: Perhaps, but I have heard of people using it successfully recently, whereas Nathaniel reported that audittrail2 seems to have obvious bugs. Thanks for the tip. Do to poor searching on my part tablelog fell under my radar. I'll try and out and see how it goes. Nathaniel Smith Web Application Developer Summersault, LLC. 710 E. Main St., Suite 200 Richmond, IN 47374 www.summersault.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] [Skytools-users] WAL Shipping + checkpoint
Sébastien Lardière wrote:
No, i don't see any change in pg_controldata, but :
there is messages in logfile :
2009-08-28 10:02:51,129 26717 INFO 000103F70088: Found
2009-08-28 10:02:51,169 26717 INFO {count: 1}
2009-08-28 10:02:51 CEST [18439]: [1862-1] user=,db= LOG: restored log
file 000103F70088 from archive
This is weird, indeed it seems that for some reason the recovery restartpoints
are not created.
Looking quickly at RecoveryRestartPoint() in xlog.c, there are two cases when it
doesn't do a checkpoint. For one thing, it checks if if enough time has elapsed
since last controlfile modification. If not, the checkpoint is skipped. I'm
wondering
if it does the correct thing if the clocks of two machines are too far apart.
Another check is is it safe to do a checkpoint. This is logged with DEBUG2, so
it should be visible if you set the logging level accordingly.
Alternatively, you could attach a debugger to the recovery process and see if
the
RecoveryRestartPoint() and CheckPointGuts() functions are called at all.
regards,
Martin
--
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] [Skytools-users] WAL Shipping + checkpoint
On Fri, 2009-08-28 at 17:55 +0300, Martin Pihlak wrote: This is weird, indeed it seems that for some reason the recovery restartpoints are not created. Looking quickly at RecoveryRestartPoint() in xlog.c, there are two cases when it doesn't do a checkpoint. For one thing, it checks if if enough time has elapsed since last controlfile modification. If not, the checkpoint is skipped. I'm wondering if it does the correct thing if the clocks of two machines are too far apart. Probably not that. Another check is is it safe to do a checkpoint. This is logged with DEBUG2, so it should be visible if you set the logging level accordingly. This seems like the most likely cause. I would guess that one of your GiST indexes has a corruption in it and is preventing a restartpoint from taking place. That has happened previously. Alternatively, you could attach a debugger to the recovery process and see if the RecoveryRestartPoint() and CheckPointGuts() functions are called at all. I would say no need for that, but you can check the Gist pending actions table. -- Simon Riggs www.2ndQuadrant.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] Anybody know where to find Dan Langille?
i think he went to myyearbook.com http://www.linkedin.com/in/danlangille lucky guy! Martin __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. To: pgsql-general@postgreSQL.org Subject: [GENERAL] Anybody know where to find Dan Langille? Date: Fri, 28 Aug 2009 10:15:01 -0400 From: t...@sss.pgh.pa.us ... or whoever is responsible for buildfarm member grebe now? The owner address recorded in the buildfarm doesn't work: 550 5.1.1 dlangi...@afilias.info... User unknown regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ With Windows Live, you can organize, edit, and share your photos. http://www.windowslive.com/Desktop/PhotoGallery
[GENERAL] Work Scheduling DB Design
Hello, I'm trying to develop a database schema to schedule and record completion of maintenance. I feel like I'm getting a bit wrapped around the wheel on this one, so I was hoping someone might be able to offer some suggestions. Here are the basic tables I've come up with: CREATE TABLE task ( task_id SERIAL PRIMARY KEY, task_name VARCHAR NOT NULL REFERENCES task_type, -- other fields omitted ); CREATE TABLE schedule ( schedule_id SERIAL PRIMARY KEY, task_id INT NOT NULL REFERENCES task, start_date DATE NOT NULL, end_date DATE NOT NULL, periodicity INTERVAL -- task is only done once if NULL ); CREATE TABLE work_effort ( work_effort_id SERIAL PRIMARY KEY, task_id INT NOT NULL REFERENCES task, completion_date DATE NOT NULL ); Here's some sample data for the schedule table: schedule_id | task_id | start_date | end_date | periodicity +-+++ 1 | 1 | 05/01/2009 | 05/30/2009 | null 2 | 2 | 06/01/2009 | 07/31/2009 | 2 weeks Which allows me to generate the following schedule: task_id | start_date | end_date ++--- 1 | 05/01/2009 | 05/30/2009 2 | 06/01/2009 | 06/14/2009 2 | 06/15/2009 | 06/28/2009 2 | 06/29/2009 | 07/12/2009 2 | 07/13/2009 | 07/26/2009 2 | 07/27/2009 | 07/31/2009 One of my objectives is to allow some flexibility in being able to change or update a schedule. For example: beginning on 7/1/09, task 2 needs to be done monthly. I don't know if this is best done by being able to update an existing schedule, or superceding old schedules with new ones. Either way, it seems like things could get a little hairy in terms of re-calculating a schedule's periods and maintaining relationships to completed work. Which brings me to my next problem: how to properly relate work efforts to a schedule. Ideally, I'd like to accomplish the following: 1. Given a work effort, determine which period of the schedule it applies to. 2. Given a schedule and some work efforts, determine if/which periods of the schedule have not had work done. 3. Enforce that work is completed within the timeframe of a schedule, or more specifically, within a specific period of the schedule. 5. Enforce that work is done order -- i.e., work needs to be done such that the periods of a schedule are completed sequentially. I'm hesitant to pre-generate work efforts based off a schedule, since changing a schedule means I'll have to regenerate the work efforts, not to mention that scheduled work might require multiple work efforts to complete. So I'd like to be able to enter in work as it's completed, and then enforce/validate that it's the _right_ work being done. In my (very limited) experience, I've found that a properly designed database makes the application side of things almost mind-numbingly easy to implement. But everything I've come up with seems like it would require some hideous triggers and/or lots of application code to implement. I don't really see a nice clean solution here, which makes me think I'm missing something. If anyone has suggestions or some experience they could offer with this, I'd greatly appreciate it. Thanks! Karl Nack Futurity, Inc. 773-506-2007 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Audit Trigger puzzler
all of my tables have 4 fields edited_by edited_date created_by created_date Most of the time, my application will set the edited_by field to reflect an application username (i.e., the application logs into the database as a database user, and that's not going to be the application user) So I log into my application as Dave, but the application connects to the database as dbuser. If the app doesn't specifically send an edited_by value in it's update, then I want to default that value to the database user. This would also be good for auditing any manual data changes that could happen at the psql level. my trigger was essentially if NEW.edited_by is null then edited_by = :current_user end if but, unfortunately, i didn't realize that in an update, the NEW variables contains a full record, so edited_by will never be null. If i do if NEW.edited_by = OLD.edited_by edited_by = :current_user end if then, if i do 2 updates to edited_by in a row, i get the DB user instead of the user i was intending to update. so, is there a way in a trigger to know if edited_by is expressly being set in the update statement? it seems like if I can know that, then i should be able to figure it out. Thanks Dave -- 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] [Skytools-users] WAL Shipping + checkpoint
On 28/08/2009 17:13, Simon Riggs wrote: Another check is is it safe to do a checkpoint. This is logged with DEBUG2, so it should be visible if you set the logging level accordingly. This seems like the most likely cause. I would guess that one of your GiST indexes has a corruption in it and is preventing a restartpoint from taking place. That has happened previously. You're right. On the master, we had an error about a gist index on a ltree column : Aug 25 09:07:02 eurovox-bdd postgres[5575]: [18-1] user=eurovox,db=eurovox_db PANIC: failed to add item to index page in appels_entrants_arbo_key The cluster try to restart ( it fail ), i had to force to stop. Since this moment, the slave didn't make any checkpoint. Now, we know why. Thanks a lot ! But how can i fix it ? -- Sébastien Lardière -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] High load on commit after important schema changes
Hi, I have following situation: - PostgreSQL 8.2.6 - ~ 1000 schemata - ~ 31k tables - ~600GB database - Linux (2.6.22, suse) - 32GB ram - disk system unknown (some raid with 3ware controllers) One of operations that happens on the database is: begin; call function(); commit; where function is plpgsql function which does: - drop several (n) views/tables - rename ~2n views and tables (and related objects like indexes and constraints) - including ones that are very often used eveyrting is fine up until commit; when commit is called load jumps from ~2 to ~40, despite the fact that there are not much activity on the system (it happens in the morning). couple of kickers: - io - doesn't show any increase - there is next to none iowait - when entering commit all cores (8 cores from intel xeon E5345) got hammered with *user* calculations. We tested the situation on 2nd system - it has ~ 75% of original data/tables/schemata, we run http siege with standard queries and then ran several times this critical transaction, and the problem never happened. Now, I know the usual is to upgrade pg, and there is plan to do it, but perhaps you have any idea on what might be going on in here? Any chance I could fix it without spending big$ on new hardware, upgrade to 8.4 and total rewrite of system? Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Update /src/tools/msvc to VC++ 2008
I have recently installed MS VC++ 2008 Express Edition for a project that interfaces with PostgreSQL. I really liked that it had tools for creating the solution and projects under VC++. However, the perl scripts create solution and project files for VC++ 2005. This causes the build to fail if you have 2008 installed since the projects need to be updated before they can be built with vcbuild. I rewrote the perl scripts to format the solution and project files for 2008 and everything works fine now. 2 questions: 1. Is there an update to these files I don't know about that could have saved me this work? 2. Can I submit my scripts and have the msvc tools reviewed/updated? Thanks, Dave Huber This electronic mail message is intended exclusively for the individual(s) or entity to which it is addressed. This message, together with any attachment, is confidential and may contain privileged information. Any unauthorized review, use, printing, retaining, copying, disclosure or distribution is strictly prohibited. If you have received this message in error, please immediately advise the sender by reply email message to the sender and delete all copies of this message. THIS E-MAIL IS NOT AN OFFER OR ACCEPTANCE: Notwithstanding the Uniform Electronic Transactions Act or any other law of similar import, absent an express statement to the contrary contained in this e-mail, neither this e-mail nor any attachments are an offer or acceptance to enter into a contract, and are not intended to bind the sender, LeTourneau Technologies, Inc., or any of its subsidiaries, affiliates, or any other person or entity. WARNING: Although the company has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
Re: [GENERAL] [Skytools-users] WAL Shipping + checkpoint
On Fri, 2009-08-28 at 17:54 +0200, Sébastien Lardière wrote: Since this moment, the slave didn't make any checkpoint. Now, we know why. Thanks a lot ! But how can i fix it ? Current issue: Rebuild standby from base backup. Cause: Locate the bug in the Index AM that causes it. Symptom: Currently index AMs don't detect situation that index is corrupt so they keep trying to recover the index for ever, even though attempting to do so permanently prevents restartpoints. I would also like them to have a mechanism for marking index corrupt and then auto-rebuild them following recovery. -- Simon Riggs www.2ndQuadrant.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] Update /src/tools/msvc to VC++ 2008
Dave Huber dhu...@letourneautechnologies.com writes: I have recently installed MS VC++ 2008 Express Edition for a project that interfaces with PostgreSQL. I really liked that it had tools for creating the solution and projects under VC++. However, the perl scripts create solution and project files for VC++ 2005. This causes the build to fail if you have 2008 installed since the projects need to be updated before they can be built with vcbuild. Count on Microsoft to arbitrarily break their customers' code :-( 2. Can I submit my scripts and have the msvc tools reviewed/updated? I believe the current Windows binaries are still being built with 2005, so unless you can change it in a backwards-compatible fashion, the odds of the patch getting rejected are pretty high. But you can try. This is the wrong list for submitting patches, however --- send to pgsql-hackers. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update /src/tools/msvc to VC++ 2008
Tom Lane wrote: Dave Huber dhu...@letourneautechnologies.com writes: 2. Can I submit my scripts and have the msvc tools reviewed/updated? I believe the current Windows binaries are still being built with 2005, so unless you can change it in a backwards-compatible fashion, the odds of the patch getting rejected are pretty high. But you can try. The other option is having a single program that can emit both formats, assuming that the differences can be kept at a minimum. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] High load on commit after important schema changes
hubert depesz lubaczewski dep...@depesz.com writes: One of operations that happens on the database is: begin; call function(); commit; where function is plpgsql function which does: - drop several (n) views/tables - rename ~2n views and tables (and related objects like indexes and constraints) - including ones that are very often used eveyrting is fine up until commit; when commit is called load jumps from ~2 to ~40, despite the fact that there are not much activity on the system (it happens in the morning). Hm, do you have forty or so idle backends hanging around while this happens? The only thing I can think of that might be causing this is shared cache invalidation messages being broadcast to all the other sessions. 8.4 has some improvements in SI messaging that might or might not solve it for you. In 8.2 I think the only thing you could do is not have so many open sessions while you change the schema. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update /src/tools/msvc to VC++ 2008
On Fri, Aug 28, 2009 at 18:19, Tom Lanet...@sss.pgh.pa.us wrote: Dave Huber dhu...@letourneautechnologies.com writes: I have recently installed MS VC++ 2008 Express Edition for a project that interfaces with PostgreSQL. I really liked that it had tools for creating the solution and projects under VC++. However, the perl scripts create solution and project files for VC++ 2005. This causes the build to fail if you have 2008 installed since the projects need to be updated before they can be built with vcbuild. Count on Microsoft to arbitrarily break their customers' code :-( They don't. They provide upgrade tools that will upgrade the format of the project files automatically for you. If your project files are developed in the normal way, you run thi upgrade on them and you're done. In our case, we re-generate them, so they have to be upgraded every time. But the tools are still there. I haven't tried it myself, but IIRC Dave Page did try that at one point, but there were some further issues. Not sure where that ended. 2. Can I submit my scripts and have the msvc tools reviewed/updated? I believe the current Windows binaries are still being built with 2005, so unless you can change it in a backwards-compatible fashion, the odds of the patch getting rejected are pretty high. But you can try. This is the wrong list for submitting patches, however --- send to pgsql-hackers. If you can produce a patch that will make it work with *both* 2005 and 2008, that will definitely be accepted. If you provide one that does only 2008, it won't. Again, I know Dave P looked into making the scripts smart enough to generate them in 2005 format and then automatically update it if you ran on 2008, not sure where it ended. That is one approach. Another approach is to have the scripts detect which version is in your environment, and generate files in that version. That really depends on how big the format difference is. If they're fairly small, this is perhaps the best way. I'd much like to see the patch that makes it run on 2008, so we can discuss which of these two methods will be best. As Tom says, please do so on the pgsql-hackers mailinglist. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] High load on commit after important schema changes
On Fri, Aug 28, 2009 at 12:28:06PM -0400, Tom Lane wrote: Hm, do you have forty or so idle backends hanging around while this happens? The only thing I can think of that might be causing this is shared cache invalidation messages being broadcast to all the other sessions. I have about 1000 backends running. more of them (like 998) are usually idling. Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- 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] High load on commit after important schema changes
hubert depesz lubaczewski dep...@depesz.com writes: On Fri, Aug 28, 2009 at 12:28:06PM -0400, Tom Lane wrote: Hm, do you have forty or so idle backends hanging around while this happens? The only thing I can think of that might be causing this is shared cache invalidation messages being broadcast to all the other sessions. I have about 1000 backends running. Ouch. You need to update to 8.4 --- the SI messaging stuff will definitely be hurting you with that many backends. Or consider using connection pooling or something to cut the number of backends. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] High load on commit after important schema changes
On Fri, Aug 28, 2009 at 12:47:15PM -0400, Tom Lane wrote: hubert depesz lubaczewski dep...@depesz.com writes: On Fri, Aug 28, 2009 at 12:28:06PM -0400, Tom Lane wrote: Hm, do you have forty or so idle backends hanging around while this happens? The only thing I can think of that might be causing this is shared cache invalidation messages being broadcast to all the other sessions. I have about 1000 backends running. Ouch. You need to update to 8.4 --- the SI messaging stuff will definitely be hurting you with that many backends. Or consider using connection pooling or something to cut the number of backends. thanks. it looks like i will not be spared this fun :) Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- 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] details locks
I understood 2009/8/28 paulo matadr saddon...@yahoo.com.br Im work with postgres my questions is,its possible on postgres: for example procpidLocker object_locked user_lockedtypeof lock comand 1 admin fooadmin2 exclusive update... thnks for all -- Veja quais são os assuntos do momento no Yahoo! + Buscados: Top 10http://br.rd.yahoo.com/mail/taglines/mail/*http://br.maisbuscados.yahoo.com/- Celebridadeshttp://br.rd.yahoo.com/mail/taglines/mail/*http://br.maisbuscados.yahoo.com/celebridades/- Músicahttp://br.rd.yahoo.com/mail/taglines/mail/*http://br.maisbuscados.yahoo.com/m%C3%BAsica/- Esporteshttp://br.rd.yahoo.com/mail/taglines/mail/*http://br.maisbuscados.yahoo.com/esportes/
Re: [GENERAL] Audit Trigger puzzler
On Fri, 2009-08-28 at 08:50 -0700, David Kerr wrote: so, is there a way in a trigger to know if edited_by is expressly being set in the update statement? it seems like if I can know that, then i should be able to figure it out. No, but you could use a before trigger to reset the value to NULL prior to each update, then your after trigger would work. -- Simon Riggs www.2ndQuadrant.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] High load on commit after important schema changes
On Fri, Aug 28, 2009 at 12:47:15PM -0400, Tom Lane wrote: hubert depesz lubaczewski dep...@depesz.com writes: On Fri, Aug 28, 2009 at 12:28:06PM -0400, Tom Lane wrote: Hm, do you have forty or so idle backends hanging around while this happens? The only thing I can think of that might be causing this is shared cache invalidation messages being broadcast to all the other sessions. I have about 1000 backends running. Ouch. You need to update to 8.4 --- the SI messaging stuff will definitely be hurting you with that many backends. Or consider using connection pooling or something to cut the number of backends. Do you have any idea on how (easily) to test if this is the cause of the situation? Will running 1000 connections to db, making each of them run query on some table, keep the connection open, and then in another connection doing the rename thing on it - work? Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- 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] A safe way to upgrade table definitions by using ALTER's
Hello! On Fri, Aug 28, 2009 at 5:56 PM, Sam Masons...@samason.me.uk wrote: Not sure if that's the sort of thing that you want/need but I don't think there's a general solution to the problem. Determining the relevant context for this sort of thing is hard. The solutions you and Steve Atkins offered seem what I have been looking for so far. Thanks. -- Sergey Samokhin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] C function doesn't return more than one tuple
Hi, I'm trying to develop a C module, but when the query result is
more than one tuple the server crashes.
Here is the code:
#include postgres.h
#include gram.h
#include utils/builtins.h
#include funcapi.h
#include executor/spi.h
#include access/heapam.h
#include fmgr.h
extern Datum sqlf(PG_FUNCTION_ARGS);
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(sqlf);
Datum
sqlf(PG_FUNCTION_ARGS)
{
char*query = TextDatumGetCString(PG_GETARG_DATUM(0));
const char *result=SELECT name FROM people WHERE age10;
int ret,proc;
int j,i;
FuncCallContext *funcctx;
int call_cntr;
int max_calls;
TupleDesc tupdesc;
TupleDesc tupledesc;
SPITupleTable *tuptable;
AttInMetadata *attinmeta;
// stuff done only on the first call of the function
if (SRF_IS_FIRSTCALL()) {
MemoryContext oldcontext;
// create a function context for cross-call persistence
funcctx = SRF_FIRSTCALL_INIT();
// switch to memory context appropriate for multiple
// function calls
oldcontext =
MemoryContextSwitchTo(funcctx-
multi_call_memory_ctx);
SPI_connect();
ret=SPI_execute(result,true,0);
proc=SPI_processed;
// total number of tuples to be returned
funcctx-max_calls = proc;
if (ret 0 SPI_tuptable != NULL){
tupdesc = SPI_tuptable-tupdesc;
tuptable = SPI_tuptable;
}
tupledesc = BlessTupleDesc(tupdesc);
MemoryContextSwitchTo(oldcontext);
}
// stuff done on every call of the function
funcctx = SRF_PERCALL_SETUP();
call_cntr = funcctx-call_cntr;
max_calls = funcctx-max_calls;
attinmeta = funcctx-attinmeta;
j=0;
if (call_cntr max_calls) { // do when there is more left to send
Datum values[tupdesc-natts];
bool nulls[tupdesc-natts];
HeapTuple tuple;
Datum datum_result;
bool isnull;
tuple=tuptable-vals[j];
for (i = 0; i tupdesc-natts; i++){
values[i]=SPI_getbinval(tuple,tupledesc,i+1,isnull);
nulls[i]=false;
}
// build a tuple
tuple= heap_form_tuple(tupledesc,values,nulls);
// make the tuple into a datum
datum_result = HeapTupleGetDatum(tuple);
j++;
SRF_RETURN_NEXT(funcctx, datum_result);
} else { // do when there is no more left
SPI_finish();
SRF_RETURN_DONE(funcctx);
}
}
--
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] High load on commit after important schema changes
hubert depesz lubaczewski dep...@depesz.com writes: On Fri, Aug 28, 2009 at 12:47:15PM -0400, Tom Lane wrote: Ouch. You need to update to 8.4 --- the SI messaging stuff will definitely be hurting you with that many backends. Or consider using connection pooling or something to cut the number of backends. Do you have any idea on how (easily) to test if this is the cause of the situation? Well, if you have something like oprofile it would probably prove or disprove the theory that sinvaladt.c is taking all the time. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question on round-robin partitioning
Purely for performance, I was looking into partitioning
some tables round-robin by value. Question is whether
there is any way to make use of this in constraint
exclusion.
Say I have a table foo with serial variable foo_id.
The partition checks are
foo_id % 8 = 0
foo_id % 8 = 1
foo_id % 8 = 2
...
If I query on foo_id % 8, explain shows the optimizer
using the constraint (1).
If I just query on foo_id = 100, the exclusion is
not used (2).
What would be the best way to feed the optimizer
enough data to use the partitioning with equality
queries?
I've come up with adding a field in the various
tables to store the id % 8 values and adding
and a.mod8_field = b.mod8_field but hopefully
there is a better way.
That or it might be a useful addition to some
later version to handle serial field % N in
the optimizer.
thanx
Example 1: explain with foo_id % 8
explain select * from foo where foo_id % 8 = 1;
QUERY PLAN
Result (cost=0.00..32.60 rows=4 width=164)
- Append (cost=0.00..32.60 rows=4 width=164)
- Seq Scan on foo (cost=0.00..16.30 rows=2 width=164)
Filter: ((foo_id % 8) = 1)
- Seq Scan on foo_1 foo (cost=0.00..16.30 rows=2 width=164)
Filter: ((foo_id % 8) = 1)
(6 rows)
explain select * from facts where identifier_id % 8 in ( 1, 3 );
QUERY PLAN
Result (cost=0.00..48.90 rows=12 width=164)
- Append (cost=0.00..48.90 rows=12 width=164)
- Seq Scan on facts (cost=0.00..16.30 rows=4 width=164)
Filter: ((identifier_id % 8) = ANY ('{1,3}'::integer[]))
- Seq Scan on facts_1 facts (cost=0.00..16.30 rows=4 width=164)
Filter: ((identifier_id % 8) = ANY ('{1,3}'::integer[]))
- Seq Scan on facts_3 facts (cost=0.00..16.30 rows=4 width=164)
Filter: ((identifier_id % 8) = ANY ('{1,3}'::integer[]))
Example 2: explain with foo_id = 1
explain select * from foo where foo_id = 1;
QUERY PLAN
--
Result (cost=4.27..131.61 rows=18 width=164)
- Append (cost=4.27..131.61 rows=18 width=164)
- Bitmap Heap Scan on foo (cost=4.27..9.61 rows=2 width=164)
Recheck Cond: (foo_id = 1)
- Bitmap Index Scan on foo_foo_id (cost=0.00..4.27 rows=2
width=0)
Index Cond: (foo_id = 1)
- Seq Scan on foo_0 foo (cost=0.00..15.25 rows=2 width=164)
Filter: (foo_id = 1)
- Seq Scan on foo_1 foo (cost=0.00..15.25 rows=2 width=164)
Filter: (foo_id = 1)
- Seq Scan on foo_2 foo (cost=0.00..15.25 rows=2 width=164)
Filter: (foo_id = 1)
- Seq Scan on foo_3 foo (cost=0.00..15.25 rows=2 width=164)
Filter: (foo_id = 1)
- Seq Scan on foo_4 foo (cost=0.00..15.25 rows=2 width=164)
Filter: (foo_id = 1)
- Seq Scan on foo_5 foo (cost=0.00..15.25 rows=2 width=164)
Filter: (foo_id = 1)
- Seq Scan on foo_6 foo (cost=0.00..15.25 rows=2 width=164)
Filter: (foo_id = 1)
- Seq Scan on foo_7 foo (cost=0.00..15.25 rows=2 width=164)
Filter: (foo_id = 1)
(22 rows)
--
Steven Lembark85-09 90th St.
Workhorse Computing Woodhaven, NY, 11421
lemb...@wrkhors.com +1 888 359 3508
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
