<div dir="ltr"><div dir="ltr"><div class="gmail_default" style="font-size:small"></div><div><span class="gmail_default" style="font-size:small"></span></div><div><div style="font-size:small" class="gmail_default"><br></div></div></div><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, 28 Mar 2022 at 04:11, Markus Demleitner <<a href="mailto:msdemlei@ari.uni-heidelberg.de">msdemlei@ari.uni-heidelberg.de</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hi Pat,<br>
<br>
On Fri, Mar 25, 2022 at 02:09:17PM -0700, Patrick Dowler wrote:<br>
> astronomical catalogues they create and curate. To that end, the tables are<br>
> added to the tap_schema and visible in the /tables endpoint. There is<br>
<br>
All tables for all users?<br>
<br>
I'm mainly asking because I'd like to do this to let "normal" people<br>
do rather ephemeral (but yet persistent for a few days or weeks)<br>
uploads, and I'm 100% sure nobody else should or want to see them.<br></blockquote><div><br></div><div style="font-size:small" class="gmail_default">Yes, in youcat all tables go into the tap_schema.</div><div style="font-size:small" class="gmail_default"><br><div><span class="gmail_default" style="font-size:small"></span>If
the caller has read permission on the schema in tap_schema, they can see
the metadata. Otherwise, the tables in that schema are invisible via
both mechanisms. I
don't recall if the schema itself is invisible or simple looking like a
directory you can't look inside, but in general permissions on the
schema hide/expose the table (existence and <span class="gmail_default" style="font-size:small">metadata).<br><br></span></div><div><span class="gmail_default" style="font-size:small">By
default, a created schema is public=false and created tables are
public=false; the owner decides if they want to make the schema public
(expose metadata about tables) and if they want to make a specific table
public (expose data in the table).<br></span></div><div><span class="gmail_default" style="font-size:small"><br></span></div><div><span class="gmail_default" style="font-size:small">Once
could support private ephemeral tables by not making the schema public.
We chose to give users control of permissions (inc. GMS support)
because of the use cases I outlined, but that would not be mandatory for
all implementers. I can even envision a way to let people create tables
but not change the permissions with our existing code, which is likely
what I'd do if we wanted to allow user ephemeral tables in our argus
(CAOM) tap service -- with our existing code I would simply have
"cadcops" own the schema and grant table creation permission to a user.
Then the user could not change schema permissions and the tables would
be invisible.</span></div></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
That's not *much* of a problem for /tables, but for tap_schema it is<br>
*quite* a complication, and hence I'm curious what you do and how it<br>
works for you.<br></blockquote><div><br></div><div><div style="font-size:small" class="gmail_default">We do provide consistent metadata from /tables and tap_schema queries. Yeah, that means row level access control injected into tap_schema queries and that bit can be complicated to implement. However, we already had figured out how to do that in argus (CAOM) because of protected metadata, so we had well tested code to inject access control constraints into ADQL queries that was easy enough to reuse for this. I can think of ways to implement this that would be much simpler in less general purpose scenarios and in standardising this whole thing, it would be important to write the spec so this kind of simplification was feasible. So if a TAP service supported ephemeral tables only visible to the owner, that simple scenario should be easier to implement than the general case like youcat, but have most of the mechanics in common and just "not allow" some things (could be literally "permission denied" or "unsupported" - TBD).<br></div></div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
> access control that the users/projects control so they can control who can<br>
> the group can see/query them) until the project publishes a paper, at which<br>
> point they would make the table publicly queryable.<br>
<br>
Rephrasing my query above: Is it just the data that's hidden or the<br>
metadata, too?<br></blockquote><div><br></div><div style="font-size:small" class="gmail_default">as above, metadata is hidden if the caller doesn't have read permission on the schema</div><div style="font-size:small" class="gmail_default"></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
> We do not (yet) put table metadata into the registry so I haven't thought<br>
> that bit through, but probably only public tables should go there and I'd<br>
> probably make it an additional manual step to "publish" (to registry) and<br>
<br>
At this point I'm a lot less worried about the registry than about<br>
what clients get from /tables and tap-schema.<br>
<br>
However, with my Registry hat on let me briefly state that for me it<br>
sucks if your tap_schema is different from what you give the<br>
registry, as that will give everyone a lot of headache when, one day,<br>
we want to move from GloTS (which harvests tap_schema if it can) to a<br>
proper Registry approach.<br>
<br>
> If you look at the details of the bulk loading, you see that it is a<br>
> streaming operation that directly inserts rows into the database. There's a<br>
<br>
Our of curiosity (not closely related to much anything): You're not<br>
batching these inserts? And that's performing well?<br></blockquote><div><span class="gmail_default" style="font-size:small"></span></div><div><span class="gmail_default" style="font-size:small">The server side does transactions in a (currently internal) batch size to balance performance and recovery from failure. The actual batch size isn't exposed or controllable by users. The user could effectively make smaller batches by splitting up the stream into a sequence of requests, but can't make them larger.<br></span></div><div><span class="gmail_default" style="font-size:small"></span> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
> clients could automatically recover from content failures. It's hard to<br>
> push 500e6 rows into a database table without failures, but that's what<br>
<br>
If find it remarkable that you seem to spend quite a bit of effort<br>
on defeating transactionality -- that's really what your users<br>
wanted? Half-uploaded tables? How does that work technically? Are<br>
you really inserting these things outside of database transactions?<br></blockquote><div><br></div><div style="font-size:small" class="gmail_default">In the original design/planning it was very clear to me that "bulk loading" was pretty simple and anything that exposes transaction semantics to the client more or less brings to bear the entire database API. Users/projects that want the latter should operate the database, have a database account, and connect to the database with an API of their choice. And they should create and manage the TAP service as well. We were/are willing to help projects with that in various ways, but this functionality is for people who don't want to do all that stuff. They typically have a big fat table with 200 columns and a few hundred million rows that they want people to query. <br></div><div style="font-size:small" class="gmail_default"><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
> -- Definitely interested in more use cases for user-generated database<br>
> content...<br>
<br>
Well, as hinted above what I'm really after is<br>
<br>
SELECT <br>
INTO my_schema.result_table<br>
ra, dec, foo, bar<br>
FROM some.tap_table<br>
WHERE...<br>
<br>
That is, people shouldn't need to download their results if they'd<br>
like to reuse them later within my database.<br></blockquote><div><span class="gmail_default" style="font-size:small">Conceptually, I think it would be feasible to make this work and have the same result.</span></div><div><span class="gmail_default" style="font-size:small"><br></span></div><div style="font-size:small" class="gmail_default">I always think of INSERT .... SELECT ... to better separate destination from source values, but they are equivalent. Obviously this would be an async job of some sort and we have thought about async loading where row data is pulled from some source (URL) but it could be a local query as well. The URL can effectively be a remote TAP query, so these cases are conceptually related.<br></div><div><span class="gmail_default" style="font-size:small"><br></span></div><div style="font-size:small" class="gmail_default"><div><div style="font-size:small" class="gmail_default"></div><br></div><div>--<br></div><div><div dir="ltr" class="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div>Patrick Dowler<br></div>Canadian Astronomy Data Centre<br></div>Victoria, BC, Canada<br></div></div></div></div></div></div><br></div></div>