Re: [GENERAL] Problems Restarting PostgreSQL Daemon

[Jeff Soules]

>  I tried following the logic, and it appears the issue now is 'invalid data
> in PID file "/var/lib/pgsql/data/postmaster.pid" '. If I delete that file,
> is it automatically recreated?

Why not just move it and rename it?  If it's recreated, great; if not,
you still have the corrupted file on hand to try to fix, no?

On Tue, Jul 22, 2008 at 11:15 AM, Rich Shepard <[EMAIL PROTECTED]> wrote:
> On Tue, 22 Jul 2008, Tom Lane wrote:
>
>> The short answer is probably "don't use Slackware's startup script". Some
>> distros have PG start scripts that have had the bugs beaten out of them,
>> and others not so much.
>
>  Excellent advice, Tom. I'll take it.
>
>> Have you read the script to see what condition causes it to issue the
>> mentioned error?  I'd imagine that it's looking at some other lockfile
>> than you think.
>
>  I tried following the logic, and it appears the issue now is 'invalid data
> in PID file "/var/lib/pgsql/data/postmaster.pid" '. If I delete that file,
> is it automatically recreated? I'm using /usr/bin/pg_ctl as user postgres.
>
> Thanks,
>
> Rich
>
> --
> Richard B. Shepard, Ph.D.   |  IntegrityCredibility
> Applied Ecosystem Services, Inc.|Innovation
>  Voice: 503-667-4517  Fax: 503-667-8863
>
> --
> 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

Re: [GENERAL] Stroring html form settings

[Jeff Soules]

On Thu, Sep 25, 2008 at 5:38 PM, Dianne Yumul <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I have some html forms that I save the settings into the database, things
> like which item was selected in the menu and if a checkbox was checked. The
> table looks like this:
>
>  user_id | report_id |  info
> -+---+
> 111 | 1 | A:CHECKEDCHECKEDCHECKED::
> 111 | 2 | A:::CHECKED::
> 111 | 3 | A::CHECKED:CHECKED::CHECKEDCHECKED:::
>
> The info column has the settings separated with a : and consecutive colons
> mean the user didn't make a selection. Would this be the way to store them?
>
> I've done some searching and I could use XML (I will read some tutorials
> after writing this email). But there may be other ways and I'm just too much
> of a newbie to know. I'm using Postgresql 8.1.11 and PHP on CentOS 5.2.
>
> Thanks in advance.
>
> Dianne

Hi Dianne,

My first thought is that if you use a combined "info" field, you'll
lose the ability to easily do any kind of meaningful data analysis
based on which boxes are checked.  If you just want to record what a
given user said at a certain time, but don't need it for any other,
more interesting evaluation of the data, then why bother with a
database?  Also, if the form structure & options etc. are at all
subject to change, you've eliminated the possibility of doing data
integrity checks and you may wind up with a code in the "info" field
that is out of date relative to what the form looks like now, i.e. the
"info" field was filled when the values corresponded to something
different from what they mean at read-time.
An XML format might be one way to record key-value pairs, and avoid
the possibility of new interpretations of the code invalidating your
old data, but from the database perspective, actually storing XML is
space-intensive, kinda clunky, and doesn't really leverage the things
you can do with a database.

If it were me, I would probably store the form values in the database
fields, so that the table's columns matched the various data inputs in
the form.  (That's if you don't expect the form structure to change a
whole lot).  Then again, I'm probably the least experienced person on
this list, so I'm sure I'll be corrected shortly : )

Best,
Jeff

-- 
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] Need schema design advice

[Jeff Soules]

On Sat, Oct 11, 2008 at 1:10 PM, Matthew Wilson <[EMAIL PROTECTED]> wrote:
> I need to track employees and their preferred locations, shifts, and
> stations.
>
> For example, I need to track that Alice prefers to work the morning
> shift at the west-side location, and she likes to work the cash-register
> station.
>
> Also, I need to track that Bob likes the west-side and north-side
> locations, likes the night shift, and likes the dishwasher station. Note
> the one-to-many relationship between Bob and his preferred locations.  I
> need to support that possibility.
>
> So, I see two ways to make my tables, and I'd like some advice.
>
> FIRST METHOD:
>
> create table preferred_location (
>employee_id int references employee (id),
>location_id int references location (id));
>
> create table preferred_shift (
>employee_id int references employee (id),
>shift int references shift (id));
>
> create table preferred_station (
>employee_id int references employee (id),
>station_id int references station (id));
>
> SECOND METHOD:
>
> create table preferences (
>
>employee_id int references employee (id),
>other_table_name text, /
>other_table_id int));
>
> In the second method, I'd store tuples like this in the preferences
> table:
>
>(, 'location', ),
>(, 'shift', )
>(, 'station', )
>
> The nice thing about the second approach is I can extend this to store
> all sorts of preferences as I dream them up.  But on the downside, I
> don't have any FK constraints.
>
> I suspect this is a pretty common dilemma.  Any commentary from the
> experts on this list is welcome.
>
> Thanks in advance!
>
> Matt


I'm certainly not an expert, but hopefully my commentary will still be
somewhat helpful.

Your "method 2" is something called an Entity-Attribute-Value table
design[1].  There was a discussion on this list a couple weeks ago
about the merits and drawbacks of designing your tables this way.

Honestly, it probably depends on what your ultimate needs are.

As the "Downsides" section of the Wiki link [1] shows, most of the
problems with EAV really start to emerge when the tables get huge and
you're dealing with hundreds of thousands to millions of entities,
each with potentially hundreds of attribute-value pairs.  If you're
intending to roll out your application for every Starbucks on your
continent, that might start to be a problem.  (From my experience,
implementations like this over large data sets suffer a big
performance hit and carry a lot of data integrity baggage.)  If you're
talking about something for use in your chain of three internet cafes
around one town, and you aren't going to have more than a dozen
Attributes per Entity, it probably doesn't matter, because the
complications will be more manageable without screwing something up.

That said, by going the EAV/"Method-2" route, you're gaining
flexibility, but at the cost of increased complication, and ultimately
repurposing a relational database to do something that isn't very
database-like, that's really more like a spreadsheet.  (So why not
just use a spreadsheet?)  You have little room for recording
additional information, like ordering preferences, or indicating that
(say) a station preference depends on a location preference, or that a
shift time depends on day of the week, etc -- so you're probably not
getting as much flexibility as you think.  Sure, you could add an
"Extra_Data" column, so you have rows:
 Marie-Location-West-1,
 Marie-Location-East-2,
 Marie-Shift-Evening-Tuesday,
 Marie-Station-Register-West,
 Marie-Shift-Morning-Sunday,
etc.  But you can see the data integrity nightmare already, when you
somehow manage to record "Marie-Shift-Register-1".  Not to mention
that you'll have to do format conversions for that "Extra_Data" field,
and incorporate logic somewhere else in your program that deciphers
whatever's in the generic data field to come up with ordering
preferences for locations, station preferences by shift times, or
whatever else you want to store.

Essentially, in my humble opinion, you're putting off the problem of
thinking about the nature and structure of your data, and most
importantly, what you're going to use that data for, when ultimately
those are the decisions that should be guiding how you design and use
the database.  Particularly given that this sounds like a
management-efficiency project rather than one that your business (I'm
assuming it's your business) hinges upon, I am imagining that you have
time
to be sure about exactly what you want to do with the data.  If you
expect that your business needs will change dramatically over the
lifetime of the product, or you don't have time to make these
decisions now, then maybe the flexibility outweighs the drawbacks.

Good luck!


[1] See e.g. http://en.wikipedia.org/wiki/Entity-Attribute-Value_model

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/m

Re: [GENERAL] PostgreSQL Object-Oriented Database?

[Jeff Soules]

On Mon, Apr 27, 2009 at 2:03 PM, Bill Moran  wrote:
> Reading between the lines, the original question was: "This guy is
> making my life difficult, and he claims it's for this reason."

I read the question more as "Did we hire some database contractor who
has no idea what he's doing?"

If the contractor is trying to sell him on something, giving the
reason that "it's that way because PG is object-oriented," then this:

> Is PostgreSQL object-oriented?  That's like asking if
> those tires are gasoline or battery powered.

is the answer to his question.
I.E. it sounds like the contractor either doesn't want to explain an
unusual design decision, or is clueless, and either way is trying to
use buzzwords to shut the client up.  I think the OP was asking us
whether the contractor's logic made sense--to make sure it really
isn't just "that's how things are done with PG" before calling him on
it.

In my opinion, it sounds like the OP should have a long talk with his
contractor about what exactly is going on, to figure out the real
reason behind these design decisions.  Maybe they're good design
decisions (we can't tell and weren't asked) but it doesn't sound like
the contractor is telling the whole story.



On Mon, Apr 27, 2009 at 2:03 PM, Bill Moran  wrote:
> In response to Eric Schwarzenbach :
>
>> Bill Moran wrote:
>> > In response to "Robert Pepersack" :
>> >
>> >
>> >> I read the document on array data types.  Do they have anything at all to 
>> >> do with PostgreSQL being "object-oriented"?
>> >>
>> >
>> > If you want to be pedantic, not really.  Technically, Postgres isn't
>> > "object-oriented", it's "object-relational".
>> >
>> > But then again, C isn't considered to be object-oriented, but I've
>> > seen some very clever object-oriented code written in C.  Of course,
>> > there are languages that have object-oriented syntax as more of the
>> > core of their design, which usually is what's meant by saying that
>> > a language is "object-oriented".
>> >
>> > Going from that statement, you could argue that PostgreSQL is very
>> > object-oriented.  Arrays are the least of the objecty features in
>> > the system: stored procedures, triggers and table inheritance are
>> > much more objectivy than arrays, although arrays could arguably
>> > be a part of Postgres' object friendliness.
>> >
>> > Looking for a more concise, more to-the-point answer?  Ask a
>> > salesperson, I'm they'll tell you whatever you want to hear.
>> >
>> >
>> >> Also, these comma-delimited fields make creating reports with our 
>> >> reporting tool impossible.
>> >>
>> >
>> > Sounds like your reporting tool is horribly limited.  Of course,
>> > if you knew that you'd be using this reporting tool, then it was
>> > your database designer's fault for not considering this limitation.
>> > If you chose the reporting tool after the database was designed, then
>> > it was a poor decision on your part.
>> >
>> > If you're looking for someone to blame (and it seems like you are)
>> > then you should just pick someone and start making up reasons.  That's
>> > what politicians do with great success.
>> >
>> > Honestly ... what are you attempting to accomplish with this thread?
>> > It seems to me that you're trying get the people on this mailing list
>> > to help you justify being angry with your database designer.
>>
>> It seems to me he's quite legitimately trying to determine if there is
>> more to his database designer's claim that these
>> comma separated fields being "object-oriented", than he might think
>> otherwise. PostgreSQL's (not very meaningful or helpful, IMO)
>> characterization of itself as an "object-relational database system" no
>> doubt leads to his very reasonable query whether he should
>> be taking something more into account than normal relational database
>> design principles.
>
> It's possible.
>
>> I think it's uncalled for to be attacking him or his motives.
>
> If that's the case, then I'm the one who will look like a arse.
>
> However, he's being very accusational of someone who isn't here to
> defend.  He's also providing no evidence for or against what he
> apparently wants us to resolve (i.e. he's posted no schema information,
> not even a partial schema).  He's also asked a question that (really)
> has no answer.  Is PostgreSQL object-oriented?  That's like asking if
> those tires are gasoline or battery powered.  object-oriented is an
> approach to programming, not a definition of a language.  I've seen
> many a program written in an "object oriented language" that was
> basically a functionally designed program.  Language features really
> mean little after the code is already written.
>
> PostgreSQL _certainly_ has a slew of features that make it conducive
> to object oriented design.  If that's the answer he's looking for,
> then there it is.
>
> Reading between the lines, the original question was: "This guy is
> making my life difficult, and he claims it's for this reason.  Is
> that reason valid?"  And I f