olesworth wrote:
> Hi Jonathan,
>
>
> On 29/03/12 19:01, Jonathan Bartlett wrote:
>
>
>
>> Now, my issue is that right now when we do updates to the dataset, we
>>> have to make them to the live database. I would prefer to manage data
>>> releases the
>
>
> by 'dataset' do you mean table, aka relation ?'
>
It's a group of tables.
> by 'not using any referential integrity', do you mean, you're NOT using
> foreign keys ('REFERENCES table(field)' in your table declaration ?
Correct.
Also, many queries cross the datasets together.
>>
>>
> by '
I have a database which contains two primary sets of data:
1) A large (~150GB) dataset. This data set is mainly static. It is
updated, but not by the users (it is updated by our company, which provides
the data to users). There are some deletions, but it is safe to consider
this an "add-only" d
I blogged about my experiences with using PG 9.1's trigram indexes, and
thought some here might be interested:
http://bartlettpublishing.com/site/bartpub/blog/3/entry/350
I would appreciate any feedback anyone has.
Jon
I blogged about my experiences with using PG 9.1's trigram indexes, and
thought some here might be interested:
http://bartlettpublishing.com/site/bartpub/blog/3/entry/350
I would appreciate any feedback anyone has.
Jon
> Actually, the database db2 has all ready been created, plus the table names
> are the same, so I can not use pg_dump and restore.
Yes you can, just use pg_dump with the -a flag, and then just use psql
with redirection rather than restore.
Jon
---(end of broadcast)-
> Also, if I wanted to put a *.pdf file in a bytea column, what functions
> do I use to escape any characters in it?
What programming language are you using?
In Perl, you do something like:
$sth->bind_param(1, $file_data, DBI::SQL_BINARY); #DBI::SQL_BINARY is
deprecated, but it works
In php you
> Do the developers generally oppose the idea of a threaded (but
> non-embedded) backend as well? If the backend is thread-safe, then users
> can still choose to run multiprocess or multithreaded right?
I've been under the impression that the developers were opposed to a
threaded server because of
PG only runs as a daemon. However, you can connect over UNIX-domain
sockets rather than Internet sockets if you want.
Jon
On Wed, 3 Dec 2003, Gregory Stone wrote:
> Is there a way from C++ to essentailly use postgresql as an embedded
> database? I have a situation where I'd rather have a postg
> Well, I have used Perl for a couple of thousand-lines-projects and while
> I can't say it's wonderful, it's usable as far as one is strict (i.e.
> code for strict and warnings), and use implicit things as less as
> possible. If you abstract things the right way it's not the nightmare
Actually,
> python that you can't do in PHP?
Python is an immensely powerful language. It is essentially the successor
to both LISP and Smalltalk. It has things like closures (well, not
complete, but pretty close), generators, a huge OO library, easy-to-use
exceptions (i.e. - in comparison to Java), and I
The problem here is really on the PHP side, because the docs of PHP don't
tell you how to create a connect string that works for local Postgres
installs (well, the docs exist, but they are hard to find).
For PEAR, it's
$db = DB::connect('pgsql://[EMAIL PROTECTED](/tmp)/DBNAME');
Jon
On Mon, 1 D
> > Java has its own issues and I am not sure it is as far supiour as you
> > are claming it is. But that is not for this dscussion.
>
> I'm not aware of any "issues" with Java (unless you mean Swing ;)).
I know for one thing - Java's lack of support for returning tuples is
hugely annoying.
Jon
> But, how can I explain to the user, who use the
> sequence numbers, that he will have to handle with
> those holes?
If it's just hte user, you might try to make sure that there are ALWAYS
holes, so he doesn't get confused.
Jon
>
> Ok! I will try to handle the holes! (fight against the
> users
Perhaps the primary key should be a sequence/serial, but also have a
secondary key which is assigned after commit.
You could have a process that continually ran something like:
select max(skey) from the_table;
select pkey from the_table where skey is null;
Then loop through the answers and assig
s converting that into a fully-escaped string for transfer, and
this is where the problem is occuring.
Any ideas?
Thanks,
Jonathan Bartlett
---(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
> Absolutely- I was gonna dig into my mod_perl manual since I'm not sure if/how I
> can make the HTML interfaces persist in Apache- I know the backend can (Apache
> <-> Pg) but instead of increasing the complexity of things this time(all the
> script does is write pages of images), I'll see how the
> Ok, I did see the autocommit flag setting in DBD:Pg when I starting reading up
> on the DBI/DBD interfacing methods so I guess I could recode for that. However,
> how do you "maintain" the current transaction open if your script is writing
> pages to the web. Even in mod_perl I think that there
> Are you serious??? How do you possibly make any money? Maybe
> I am just naive but here at CMD a flat rate quote is a minimum of 2.5x
> what we would normally charge JUST IN CASE the client (which will happen)
> decides to change direction midstream.
>
We _plan_ for revisions. We even have a fo
We dont' do hourly rates. We do it by the job. Therefore, the consumer
doesn't have to think about how much they're paying us per hour. The just
have to think about the benefits in relation to the cost. It also allows
us to get better profit margins, because we can automate in innovative
ways,
> if you, then write a program for that. psql is a database server, not a
> client program.
No, psql is a client program. postmaster is the database server.
Jon
>
> C.
>
> Daniel Schuchardt wrote, On 9/17/2003 8:37 PM:
> > Hm, but lo_import/lo_export imports/exports from the local filesystem.
rt for what is called "large object" which is I think what I
> might want. If you or anyone else out there has worked with bytea's with
> module, I appreciate some direction and any other wisdom (e.g. pros/cons,
> catch22's, etc). Thanks!
>
> Learning everyday...
I'm a big fan of bytea. In every case where I've done the filesystem
method I wished I hadn't.
Jon
On Tue, 9 Sep 2003, Edwin Quijada wrote:
> Hi !! Everybody
> I am developing app using Delphi and I have a question:
> I have to save pictures into my database. Each picture has 20 o 30k aprox.
>
>
> How would that possibly make any sense? A view is supposed to be a
> self-contained, logically consistent object ... not something that
> exerts magical action-at-a-distance powers upon queries that reference
> it.
Oh, I see, you just don't love us enough to give us magical powers to our
view
> I tried to use "left join" to select data from my
> database. Result is come out, but I didn't see the
> value of "displayString". I added "left outer join",
> same result.
sounds like deeviceTypesEnum.enumID don't correspond to
devices.productType.
Jo
>
> SELECT devices.ProductType, device
> I think I have found out why.. I have a where clause on a ID field but it
> seems like I need to cast this integer to the same integer as the field is
> defined in the table, else it will do a tablescan.
Yes, this is correct
> Is this assumtion correct? And if it is, do I then need to change al
However, that doesn't cover the case where you want to update the record
if it already exists.
Jon
> insert into test (a, b, c, d)
> (select 1, 2, 3, 4 where not exists
> (select 1 from test where a=1 and b=2 and c=3 and d=4)
> );
>
> If your table contains a=1, b=2, c=3, and d=4, nothing
> No it don't know anything about the table it insert into. I simply do
> the following :
>
> 1. INSERT data (comming from another layer)
> 2. Get the last oid
> 3. SELECT * FROM the same table where oid = what I just found.
>
> I know absolutly nothing about the table, and I like it this way :-)
> And doing the explicit cross join statement on o_kat_prod instead of
> ot_kat_prod gives the expected performance to me ( 7.42 msec instead
> of 7324.49 msec with EXPLAIN ANALYZE).
>
> Do i've any chance to get the same performance on the view?
I've had this problem and it was due to improper ty
You can do HA from the OS level:
Have a SCSI disk array shared between two computers
Use a journalling filesystem on this array
Have a "serial kill switch"
Have a program that checks if each server is alive.
If the primary goes down, the secondary will:
a) Shut off the power to the primary
b) Mo
> Online backups with archived transaction logs are the next big killer feature
> (the last one remaining?) for 24x7 operation I think.
I believe this is at least theoretically possible using Linux device layer
tricks. Using network block devices, you can have a network RAID1, with
the transactio
Is there a way to get an interval in a standard format? It seems like it
keeps changing it's ouput style based on the time length.
Jon
On Mon, 18 Aug 2003, Bruno Wolff III wrote:
> On Mon, Aug 18, 2003 at 16:09:43 +0200,
> Bjørn T Johansen <[EMAIL PROTECTED]> wrote:
> > I need to compute the
What would be really cool (although a lot harder to implement) would be
the ability to generate a hierarchical XML document when using foreign key
relationships. Trying to tell PG how to format that might be a bit of an
issue, though.
Jon
On Thu, 7 Aug 2003, Gavin M. Roy wrote:
> Add an
> ech
> Not *one* table. I never advocated that. It is perfectly normal to split
> your data into different tables *vertically* (i.e. things that do not
> have any intersection between their data, should go into different
> tables), but it very rarely (if at all) makes any sense to split it
> *horizontal
> Your programmers must be really smart :-)
> Are you saying that you have never seen a person writing a piece of sql
> like:
> insert into mytable (id, data) select max(id) + 1 from mytable, 'mydata'
> ???
>
> If so, you must be really lucky :-)
>
I would never hire such a person.
> Exactly. But
NOTE - after writing all this, I did think of a possible solution, but I'm
not sure if PG can handle it. If I made a table called "object" with one
column, the object_id, and then had EVERY table inherit from this table.
Then, I could have my constraints set up against this master table. (I'm
not
> So, how can you possibly tell when looking at your note which entry it
> applies to?
That's easy - these are always referred from the table, never to the
table. In the few instances where I go the other way, it's limited to 2
or 3 tables, and I do separate joins combined with a UNION.
> When y
Why not just drop the "references" clause? I mean, the point of having
transactions is to guarantee integrity within a transaction, if you're not
going to have that, why even bother with the clause?
Most of my databases don't even user "references", just because I like the
flexibility, and I have
Questions:
1) If you have an index on a cacheable function, does PostgreSQL use the
index instead of calculating the results?
2) How does PostgreSQL know when to recompute the function?
Jon
On Fri, 25 Jul 2003, Elielson Fontanezi wrote:
> Thanks a lot!
>
> The complete solution is here!
>
> 1s
Has anyone tried PostgreSQL in a MOSIX-like cluster?
Jon
On Fri, 18 Jul 2003, [ISO-8859-1] Jordi Sánchez López wrote:
> Can two postgresql processes (running in different machines) access and
> work with the same database files in a shared storage scenario? Would
> there be any problem?
>
> Than
I would add a nohup.
In addition, are you using the Solaris performance/process monitoring
tools? I forget which one, maybe pfiles, would occasionally send wierd
signals to processes. Check your cron listings to see if you're running
anything like that.
Jon
---(end of
I have created a patch to postgresql 7.3.3 to make pg_dump more CVS
friendly. Basically, pg_dump numbers all of it's TOC entries. Therefore,
if you modify or recreate a view that used to be at the beginning, then
ALL of your tables/indexes/etc get renumbered, which causes a lot of junk
in your CV
> The original developers didn't really have a concept of storing different
> info in different tables.
That kind of stuff drives me nuts. Where do people get their CS degrees?
It took me less that 2 days to teach our ARTISTS how to construct
fully-normalized tables (it's a long story as to why I
I know the current method for specifying alternate drives for PG tables is
by using symlinks. I had some ideas for simple ways to do this in PG
code, but wanted to know if anyone was working on this right now. I'd
hate to take the time to start messing with this if others were already on
it.
Jon
I haven't used this. It would probably work, but there's also another
way just using plain SCSI. You can attach both your main machine and a
hot standby to an external RAID array. When the main machine goes down,
just mount the RAID array yourself. However, you should also install a
serial powe
> > The problem is that you can't do the check on the _field_ since it has
> > already been converted to a date.
>
> You're right. How about a "before insert" trigger?
This suffers from the same problem, I believe. If I weren't a lazy
bastard, I check it :)
Jon
---(end
46 matches
Mail list logo
