Re: dbeaver

[negora]

On 01/04/2020 18:17, Roberto Della Pasqua wrote:
> Hello, please some questions about a newbie Postgresql user:
>
> - what is the best method to keep the data replicated (for backup purposes):
> Can be possible to deploy a master/slave with realtime data replication and 
> adding a storage where do a journaled backup of the database, eg. a backup 
> incremental or GTF schema files? Which commands works better at low level for 
> background backup/restore?
No idea.
> - does dbeaver is a good frontend for pg administration?

That's the client that I use most often, and I love it. It has lots of
useful features, such as good query completion, row coloring, virtual
columns, virtual foreign keys, export to multiple formats, etc. Years
ago I missed some features from pgAdmin III, but nowadays its support
for PostgreSQL is excellent. And you can use the same GUI for multiple
database engines, which is great.

Obviously, there are tasks that you should do (or only can do) with pure
SQL. But DBeaver saves you a lot of time for the most common tasks.

>
> Roberto Della Pasqua
> www.dellapasqua.com
>

Re: dbeaver

[negora]

  
  
Both features reside only in the client side, of course.

A virtual column allows to permanently show, in a table, a column
  calculated from a _javascript_-alike _expression_. That _expression_ is
  based on other columns of the table and offers several
  mathematical functions. I know that you can do the same with a
  view, which is much more powerful, but doing this in the
  client-side has some advantages in certain situations:

  If you have not permission to create a view.

  
  If you have permission, but don't want to clutter the schema.
So it's not invasive to other users.

  
  You can use this column to color the rows by a given criteria.

  
  Maybe the most important: since you're viewing a table and not
a view, you can still edit the records from the GUI, as usual.
  

Virtual foreign keys, on the other hand, didn't seem interesting to
me... Until I had to work with a database from Microsoft Navision in
SQL Server. Navision don't use foreign keys at the database level.
So, in order to figure out the relationships among tables, I had to
add virtual FKs. That way, I had a permanent visual aid. That
feature, combined with the auto-diagrams generated by DBeaver,
allowed me to understand part of that mess. It was my salvation.


On 01/04/2020 22:28, Thomas Kellerer
      wrote:

negora
  schrieb am 01.04.2020 um 21:44:
  
  It has lots of useful features, such as
good query completion, row

coloring, virtual columns, virtual foreign keys

  
  What kind of feature is "virtual foreign keys"?
  
  
  Or "virtual columns" in the context of a SQL GUI tool
  
  
  
  
  

  

Re: Off-topic? How to extract database statements from JPA?

[negora]

Hi Gus:

JPA (Jakarta Persistence API; previously known as Java Persistence API) 
is "just" the specification. There are several implementations of this. 
The reference implementation is EclipseLink, but there are others widely 
used such as Hibernate or OpenJPA. You should ask which one is being 
used in your company.


The SQL code is generated on the fly, so there is no place where it's 
stored. However, there are ways to view what SQL is generated in real 
time. For example, with Hibernate, you can change the level of the 
"org.hibernate.SQL" logger to "DEBUG", and view what happens behind the 
scenes.


You could also enable the logging in the PostgreSQL server-side and see 
what's happening.


Best regards.


On 31/10/2022 11:50, Gus Spier wrote:
I apologize if this is off-topic, but I've become exceedingly 
frustrated and need help.


The assignment is to evaluate SQL code for an application that has 
been built with Java Springer Persistence API and, if appropriate, 
offer suggestions to improve database performance.


The problem is that I am not a Java guy. I can usually read code and 
determine what it is trying to do. But here, I am at a loss. Where 
does the JPA hide the SQL code? I do not really expect a definitive, 
explicit answer, but if anybody could point me to documentation or a 
working aid that lays out where the JPA stores the DDL and DML, I 
would be truly grateful.


Thanks in advance.

Gus

Re: Get more columns from a lookup type subselect

[negora]

Hi Chris:

You can use a subselect in the `from` and `join` clauses. That's how I 
get multiple columns from a single subselect.


If the subselect needs to use a reference from the outer scope (i.e. the 
main query) , you can use the `lateral` modifier.


Best regards.


On 10/03/2023 08:34, Durumdara wrote:

Dear Members!

I use the subselects many times in my Queries to get some info (Name, 
etc) from a subtable.


Sometimes I have to use it to get the last element.

select t.*,
   (

select value from u join ... where ...

order by id desc limit 1

   ) as last_value,

It is ok, but how can I get more values from subselect without 
repeating the subquery?


select t.*,
   (

select value from u join ... where ...

order by date desc limit 1

   ) as last_value,
   (

select type from u join ... where ...

order by date desc limit 1

   ) as last_type,

This is not too comfortable, and may make mistakes if the join is not 
defined properly or the date has duplicates.


Ok, I can use WITH Query:

with
  pre as ( select * from t  )
  ,sub as (select pre.*, (select u.id  from u where ... 
limit 1) as last_u_id

select  sub.*, u.value, u.type, u.nnn from sub
left join u on (u.id  = sub.last_u_id)

But sometimes it makes the Query very long (because I have to read 
more subselects).


Do you know a simple method for this, like:

select t.*,
   (

select value, type, anyfield from u join ... where ...

order by date desc limit 1

   ) as last_value, last_type, anyfield

?

Thank you for the help!

Best regards
Chris

Re: Get more columns from a lookup type subselect

[negora]

For example, if you wanted to list book authors and the latest book of 
each one, I would do something like this:


    SELECT
        author.first_name,
        author.surname,
        latest_book.title,
        latest_book.release_date
    FROM author
    CROSS JOIN LATERAL (
        SELECT book.title, book.release_date
        FROM book
        WHERE book.author_id = author.id
        ORDER BY book.release_date DESC
        LIMIT 1
    ) AS latest_book;



On 10/03/2023 11:00, Durumdara wrote:

Dear Negora!

Can you show me the usage with some short examples?

Thanks for it!

BR
   dd


negora  ezt írta (időpont: 2023. márc. 10., P, 9:43):

Hi Chris:

You can use a subselect in the `from` and `join` clauses. That's
how I get multiple columns from a single subselect.

If the subselect needs to use a reference from the outer scope
(i.e. the main query) , you can use the `lateral` modifier.

Best regards.


On 10/03/2023 08:34, Durumdara wrote:

Dear Members!

I use the subselects many times in my Queries to get some info
(Name, etc) from a subtable.

Sometimes I have to use it to get the last element.

select t.*,
   (

select value from u join ... where ...

order by id desc limit 1

   ) as last_value,

It is ok, but how can I get more values from subselect without
repeating the subquery?

select t.*,
   (

select value from u join ... where ...

order by date desc limit 1

   ) as last_value,
   (

select type from u join ... where ...

order by date desc limit 1

   ) as last_type,

This is not too comfortable, and may make mistakes if the join is
not defined properly or the date has duplicates.

Ok, I can use WITH Query:

with
  pre as ( select * from t  )
  ,sub as (select pre.*, (select u.id <http://u.id> from u where
... limit 1) as last_u_id
select  sub.*, u.value, u.type, u.nnn from sub
left join u on (u.id <http://u.id> = sub.last_u_id)

But sometimes it makes the Query very long (because I have to
read more subselects).

Do you know a simple method for this, like:

select t.*,
   (

select value, type, anyfield from u join ... where ...

order by date desc limit 1

   ) as last_value, last_type, anyfield

?

Thank you for the help!

Best regards
Chris

Re: Return rows in input array's order?

[negora]

Hi Dominique:

Take a look to the "unnest()" function. It transforms an array into a 
set of rows. I believe I used it in the past to do something similar to 
what you need.


Another option is to use a "values" expression (in a subquery) instead 
of an array, and build the query dynamically.


Best regards.


On 09/05/2023 11:37, Dominique Devienne wrote:
On Tue, May 9, 2023 at 11:23 AM David Wheeler  
wrote:


> Hi. With an integer identity primary key table,
> we fetch a number of rows with WHERE id = ANY($1),
> with $1 an int[] array. The API using that query must return
> rows in the input int[] array order, and uses a client-side
> mapping to achieve that currently.
>
> Is it possible to maintain $1's order directly in SQL? Efficiently?

We’ve done this before with an “order by array_index(id,
input_array)”. I forget the actual function consider that pseudo code


Thanks David. I see how this would work.

It was only used for small arrays but never noticed any
performance issues


Hmmm, sounds like this would be quadratic though...

Each call to array_index() will be O(N), so turn the sort into O(N^2) 
just from the array_index() calls,
without even considering the sorting itself (which I assume is O(N log 
N)).


I wonder whether the int[] can be turned into a pseudo table with a 
ROWNUM extra generated column that
would then be (LEFT) JOIN'd to the accessed table, so that the 
original array index is readily accessible.

Would something like this be possible in Postgres' SQL?

I could then skip the sort, return that original index as part of the 
select,
and thus be able to read the other columns directly in the correct 
client-side re-allocated vector-slot / structure...

Re: My 1st TABLESPACE

[negora]

Hi:

Although the "postgres" user owns the "data" directory, Has he access to 
the whole branch of directories? Maybe the problem is that he can't 
reach the "data" directory.


Regards.


On 07/08/2023 07:43, Amn Ojee Uw wrote:


I'd like to create a TABLESPACE, so, following this web page 
,  
I have done the following :


*mkdir 
/home/my_debian_account/Documents/NetbeansWorkSpace/JavaSE/Jme/database/postgresql/data*


*sudo chown postgres:postgres 
/home/my_debian_account/Documents/NetbeansWorkSpace/JavaSE/Jme/database/postgresql/data*


*sudo -u postgres psql*

*\du**
** arbolone    | Cannot login  | {}**
** chispa |    
| {prosafe}**

** workerbee | Superuser, Create DB | {arbolone}**
** jme |    | 
{arbolone}**
** postgres    | Superuser, Create role, Create DB, Replication, 
Bypass RLS | {}**

** prosafe  | Cannot login  | {}**

**CREATE TABLESPACE jmetablespace OWNER jme LOCATION 
'/home/my_debian_account/Documents/NetbeansWorkSpace/JavaSE/Jme/database/postgresql/data';*


The *CREATE **TABLESPACE* schema throws this error message :

/*ERROR:  could not set permissions on directory 
"/home/my_debian_account/Documents/NetbeansWorkSpace/JavaSE/Jme/database/postgresql/data": 
Permission denied*/


I have followed the web page to the best of my abilities, and AFAIK, 
the postgres user owns the folder '*data*'.


I know that something is missing, where did I go wrong and how can I 
resolve this issue?



Thanks in advance.

Re: How to reference a DB with a period in its name ?

[negora]

Hi:

I think your problem is in the Perl side more than in PostgreSQL's. When 
you said you tried escaping with \, Did you mean *exactly* this?


   "delete from \"thedb.v1\".theschem.thetab where col1 = 1"

I think this should work.

Or you can use single quotes to enclose the query, so that you don't 
need to escape the double quotes:


   'delete from "thedb.v1".theschem.thetab where col1 = 1'

Best regards.


On 29/03/2024 22:16, David Gauthier wrote:

Hi:

I have a DB named "thedb", which has a schema called "theschem" which 
has a table called "thetab".  In a perl/DBI script, I am able to work 
with the table by referencing it as... "thedb.theschem.thetab" as 
in... "delete from thedb.theschem.thetab where col1=1"


No problem (so far...)

New DB now has a name "thedb.v1" (notice the "."), the schem and table 
are the same as above.  But now I'm getting a syntax error presumably 
because of that "." introduced in the DB name.


I tried encapsulating the DB name in double quotes (no good), single 
quotes (still no good) escaping with '\' (no good), escaping with ".." 
(no good).


Is there a way I can reference the DB which has the "." in it's name ?

v11.5 on linux (I know, way past time to upgrade)

Thanks in Advance !

Why does it sort rows after a nested loop that uses already-sorted indexes?

[negora]

Hi:

I've a question regarding nested loops and the order in which they 
return rows. Can you help me, please?


Suppose that I've two tables:

    - Table [sales_order]

    * Columns [id]
    * Index [sales_order_pkey] on [id]

    - Table [order_line]

    * Columns [id], [sales_order_id]
    * Index [order_line_ukey] on [sales_order_id], [id]

Then, I run the following query:

---

SELECT sales_order.id, order_line.id

FROM main.sales_order

JOIN main.order_line ON order_line.sales_order_id = sales_order.id

WHERE sales_order.customer_id = 2

ORDER BY sales_order.id, order_line.id;

---

The query planner decides to use the following nested loop:

---

Incremental Sort  (cost=26.90..16020.06 rows=144955 width=8)

  Sort Key: sales_order.id, order_line.id

  Presorted Key: sales_order.id

  ->  Nested Loop  (cost=0.70..4593.99 rows=144955 width=8)

    ->  Index Scan using sales_order_pkey on sales_order  
(cost=0.28..19.31 rows=79 width=4)


  Filter: (customer_id = 2)

    ->  Index Only Scan using order_line_ukey on order_line  
(cost=0.42..39.22 rows=1869 width=8)


  Index Cond: (sales_order_id = sales_order.id)

---

As you can see, the planner does detect that the outer loop returns the 
rows presorted by [sales_order.id]. However, it's unable to detect that 
the rows returned by the inner loop are also sorted by [sales_order.id] 
first, and then by [order_line.id].


Why is it? Is it because the planner is designed to always ignore the 
order of the inner loop, even although it could take advantage of it 
(for example, because the analysis time rarely is worth it)? Or is there 
something that I'm missing?


If I'm not mistaken, in this case both index scans seem to be done 
serially, in an N x M style, so I think the row order would be 
preserved, Right?


Thank you!

negora

Re: Why does it sort rows after a nested loop that uses already-sorted indexes?

[negora]

> That's a level of analysis that it doesn't do...

Great. I suspected that, but I needed a confirmation from a reliable 
source. Thank you!


> ...and TBH I'm not even
> entirely sure it's correct to assume that the output is sorted like
> that.  At minimum you'd need an additional assumption that the
> outer side's join key is unique, which is a factor that we don't
> currently track when reasoning about ordering.

Ouch! I hadn't thought about that possibility! When I tried to mentally 
reproduce the nested loop, I always considered the values of the outer 
loop to be unique. I guess that was because, very often, I used unique 
indexes for my tests... But it doesn't have to be so, of course.


Best regards.



On 18/04/2024 16:53, Tom Lane wrote:

negora  writes:

As you can see, the planner does detect that the outer loop returns the
rows presorted by [sales_order.id]. However, it's unable to detect that
the rows returned by the inner loop are also sorted by [sales_order.id]
first, and then by [order_line.id].


That's a level of analysis that it doesn't do, and TBH I'm not even
entirely sure it's correct to assume that the output is sorted like
that.  At minimum you'd need an additional assumption that the
outer side's join key is unique, which is a factor that we don't
currently track when reasoning about ordering.

regards, tom lane

Re: DBeaver does not show all tables in a Schema

[negora]

Are you sure that you're not applying a filter in the "Database 
Navigator" panel of DBeaver?


Sometimes, it occurs to me that I apply a filter for certain database, I 
forget to remove it, then open another database, and some or all tables 
do not appear in the navigator.



On 29/10/2021 12:46, Shaozhong SHI wrote:
I used a DBeaver to connect to postgres but it does not show all 
tables in a schema.


Can anyone shed light on this?

Regards,

David